RE: SQL Pivot

["Leland, David" <dleland@xxxxxxxxxx>]

Title: RE: SQL Pivot

Based on info from Pete Hall, I have my SQL "pivot" working and thought I'd share it since it looks like something that will come in real handy for me.

The file contains just 3 fields - ITNBR, TDATE (as *DATE data type) and QTY.  Following is the SQL statement:

select itnbr,                                                      
decimal(sum(case when month(tdate) = 1 then qty else 0 end),7,0) as Jan,
decimal(sum(case when month(tdate) = 2 then qty else 0 end),7,0) as Feb,
decimal(sum(case when month(tdate) = 3 then qty else 0 end),7,0) as Mar,
decimal(sum(case when month(tdate) = 4 then qty else 0 end),7,0) as Apr,
decimal(sum(case when month(tdate) = 5 then qty else 0 end),7,0) as May,
decimal(sum(case when month(tdate) = 6 then qty else 0 end),7,0) as Jun,
decimal(sum(case when month(tdate) = 7 then qty else 0 end),7,0) as Jul,
decimal(sum(case when month(tdate) = 8 then qty else 0 end),7,0) as Aug,
decimal(sum(case when month(tdate) = 9 then qty else 0 end),7,0) as Sep,
decimal(sum(case when month(tdate) = 10 then qty else 0 end),7,0) as Oct,
decimal(sum(case when month(tdate) = 11 then qty else 0 end),7,0) as Nov,
decimal(sum(case when month(tdate) = 12 then qty else 0 end),7,0) as Dec
from file
group by itnbr
order by itnbr

Dave