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:
PartNum__Yr-Mnth__Qty
Part123___Jan03____88
Part123___Feb03____33
Part123___Mar03____06

What I would like to output is:

PartNum___Jan03__Feb03__Mar03
Part123_____88_____33_____06

Here is a (simple) example of my current SQL:
Select PartNum, Date, Qty
From Table1
where
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.

Any suggestions?

Thanks!

select Distinct PartNum,
cast(0 as decimal(15,0)) as Jan03,
cast(0 as decimal(15,0)) as Feb03,
cast(0 as decimal(15,0)) as Mar03,
into #temp
from Table1

Then from there run your regular query, put in a temp table.

Then from there you can update the columns in the above table.

Its ugly but works.

You can also do subselects.

Hope this gets your mind rolling

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)


select
pm.HostPartID,
pm.partcustom1,
lt.loctypename,
lm.loccustom5,
lm.HostLocID,
to_char(dh.HistoryBegDate,'yyyy mm') "Yr-Mnth",
dh.historyamount,
dh.historyschamount
from
DEMAND_HISTORY dh,
PART_MASTER pm,
LOCATION_MASTER lm,
LOC_TYPE lt
where
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')
order by
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.

If you are fine with dynamically generating SQL(in case you need variable number of columns), you can use something like

select col1, col2,
sum(case when month(datecol1)=1 then value1 else 0 end) month1,
...
sum(case when month(datecol1)=12 then value1 else 0 end) month12
from table1 .....
where ...
group by col1, col2