RE: SQL Pivot

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

Title: RE: SQL Pivot

V4R4M0.  I think that CASE wasn't implemented until after V4R2M0.

Dave

-----Original Message-----
From: Klein Ron [mailto:ron.klein@brctsg.com]
Sent: Thursday, February 03, 2000 3:15 PM
To: 'MIDRANGE-L@midrange.com'
Subject: 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
+---