Unanswered: Creating Output in columns instead of Rows
Another query I am having difficulties on is creating a SQL (Oracle) query that takes some values in monthly buckets & outputs in columns instead of a new row for each value (Cross Tab Query in Access).
Right now my output would look like this:
Here is a (simple) example of my current SQL:
Select PartNum, Date, Qty
Date >= To_Date('01/01/2003','mm/dd/yyyy') and
Date <= To_Date('01/31/2004','mm/dd/yyyy')
Order by PartNum, Date
(Qty is really 2 fields added together and there are some table joins along with a few more fields that still would be only 1 of)
I have seen an example of PIVOT, but could not get that to work.
Thanks for the reply. Guess I should not have made my example so simple!
The date actually comes from (part of select statement):
to_char(dh.HistoryBegDate,'yyyy mm') "Yr-Mnth"
So the "field" Yr-Mnth is not a table field, but created through the select statement.
The Qty comes from:
(dh.historyamount + NVL(dh.historyschamount,'0')) as "Qty"
There is only 1 value per month for each value.
In this example, the output would have 13 columns of demand data, 1 listed for each month.
As I would not want to change the CAST statement(s) each time the report is run (could be for 1 month of data or 24 months, depending on what the requester wants), hard coding each CAST statement is not what I would be looking to do.
Here is my current SQL. Due to the number of part / location / month combos, I am getting about 500K lines of data I am then importing into Access & then creating 1 row of data for each part / location combinations with the months listed off to the side.
If I could get the months to be in columns insead of a unique row, the output would be reduced from 500K lines to about 42K lines & would be in the format the users want instead of having to use Access as an inbetween step to create the deisred output. (also much smaller to download & could fit on a spreadsheet)
to_char(dh.HistoryBegDate,'yyyy mm') "Yr-Mnth",
pm.PartID = dh.PartID and lm.LocID = dh.LocID and lm.loctypeid=lt.loctypeid and
(dh.historyamount > 0 or NVL(dh.historyschamount,'0') > 0 ) and dh.HistoryBegDate >= To_Date('01/01/2003','mm/dd/yyyy') and dh.HistoryBegDate <= To_Date('01/31/2004','mm/dd/yyyy')
pm.HostPartID, lt.loctypename, lm.HostLocID, dh.DemandStreamId
(I don't have to use (+) in my table joins as there will always be a match)
Guess placing the data into columns instead of rows is not as simple as I had hoped!?!
well this is a daunting task that our end users want. I ask myself why cant they just read the data the other way, its all the same.
Well the solution to your problem is not hard but not simple either. If you follow the same principles you can create a dynamic sql statement that can create everthing for you. Its just a process of automation that we all live with.
You do not have to have a hard coded yyyymm column, you can build this to where each column is based on date functions. That is the way I do it so I do not have to ever touch the freaking stored procedure again. Takes some playing around with, but its definatly do able, and worth the few extra hours it takes to code it. Just becarefull to consider the change in years when converting to yyyymm when they roll over.
Let me know Monday if you have not figured it out, I am leaving the office.