Re: SQL pivot question

["David Morris" <dmorris@xxxxxxxxxxxxx>]

Bart,

You should be able to include another level by specifying a SELECT with the 
alternate grouping level in your FROM clause.  So you have something like:

Select T1."CHL1UC" "c1",... T3.FIELDX
from ("COGNOSANC"."GROEPS" T1 LEFT OUTER JOIN "COGNOSANC"."FLCUS" T2 on
T1."CHL1UC" = T2."F1CUNO") ...      join (SELECT sum(field1) as fieldx from 
COGNOSANC 
group by field2)  T3 on T1.field = T3.field        ...order by 
"c1","c5","c6","c7","c8","c9","c10"

I do not know your fields well enough to give a real example, but that should 
get you 
a good start.

David Morris

>>> bart.verweijen@advalvas.be 11/13/00 07:17AM >>>
Hi,

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",
T2."F1CUNM" "c2",
T2."F1CUA1" "c3",
T2."F1CUA3" "c4",
T1."F5CFI3" "c5",
T1."F5CFI1" "c6",
T1."F6ITTY" "c7",
T1."F6ITGR" "c8",
T1."ITNOUC" "c9",
T1."F5ITDS" "c10",
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 !

Thanks for your time,
Bart Verweijen


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