Re: SQL pivot question

At 08:17 11/13/2000, Bart Verweijen wrote:
>I found some interesting info on my question in the archives, but not
>everything.
>I want to build a crosstab with following sql statement:
>
>select
>T1."CHL1UC" "c1",
<snip>
>decimal(sum(case when jaar='2000' then ofqsuc0101 else 0 end),15,8) "Qty
>2000",
>decimal(sum(case when jaar='1999' then ofqsuc0101 else 0 end),15,8) "Qty
>1999",
>decimal(sum(case when jaar='2000' then ofqsuc0101 else 0
>end),15,8)/decimal(sum(case when jaar='1999' then ofqsuc0101 else 0
>end),15,8) "Qty Index"
>from ("COGNOSANC"."GROEPS" T1 LEFT OUTER JOIN "COGNOSANC"."FLCUS" T2 on
>T1."CHL1UC" = T2."F1CUNO")
>where chl1uc='K02015'
>group by T1."CHL1UC" , T2."F1CUNM", T2."F1CUA1" , T2."F1CUA3" , T1."F5CFI3"
>, T1."F5CFI1", T1."F6ITTY", T1."F6ITGR", T1."ITNOUC", T1."F5ITDS"
>order by "c1","c5","c6","c7","c8","c9","c10"
>
>This works so far. This gives me sums on the lowest level of my query. But I
>also need sums on higher group levels... Anyone know if this is possible ?
>It would help me A LOT !

I guess it depends on what you're trying to accomplish. Obviously, if 
you're looking for aggregates of the detail data in a report, the way to do 
that would be to add things up as you process the detail. One powerful 
thing about SQL though, is that it allows you to build views on top of 
views. If you need the higher level totals on every row, you could use 
another view with the higher level summary data in it, and join that to the 
existing view. If you want to insert the higher level data one time, as you 
would do during a control break, you might be able to work that out with a 
another view and a union. I've not tried that. The sequencing would be the 
tricky part. Maybe you could insert a level break field in the order by 
clause to accomplish that. Performance might be disappointing though. 
Processing overhead can add up quickly when you build views on views.

hth
Pete

Pete Hall
pbhall@execpc.com
http://www.execpc.com/~pbhall/

+---
| 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
+---