RE: SQL Pivot

Dave,
What version of OS are you running.  I tried your pivot SQL on our  box and
it does not like something in the statements you use.  We are on V4R2.
Ron

> -----Original Message-----
> From: Leland, David [SMTP:dleland@Harter.com]
> Sent: Thursday, February 03, 2000 9:01 AM
> To:   'MIDRANGE-L@midrange.com'
> Subject:      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 
> 
+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---