SQL issue? Can this be done?

apattin

HI All,

I need some SQL help. I have a table that looks like this:

Cat1 Cat2 Cat3 Subset Number
---------------------------------------------
A ABC Primary X 21
A ABC Primary Y 3
A ABC Secondary X 11
A ABC Secondary Y 10
A ABC <null> X 12
A DEF Primary Y 4
A DEF Secondary X 22
A DEF Secondary Y 15

We know that there are only 2 subsets (X and Y);
3 cat3 values (Primary, Secondary and null). We
do not know the number of Cat2 or Cat1 values; Cat2 may be null but
Cat1 is not.
I want to get this format in a result set:

Cat1 Cat2 X-Primary X-Secondary X-null Y-Primary Y-Secondary Y-null
--------------------------------------------------------------------
A ABC 21 11 12 3 10 <null>
A DEF <null> 22 <null> 4 15 <null>

I suspect it's a combiantion of GROUP BYs and CASE, but I can't find
the correct syntax. Is this even possible without some procedural code?

Apr 4 '06 #1

Serge Rielau

apattin wrote:

I suspect it's a combiantion of GROUP BYs and CASE, but I can't find
the correct syntax. Is this even possible without some procedural code?

Correct. There is one more incredient: MAX()

MAX(CASE WHEN cat3 = 'Primary' then number END) AS "X-primary"

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Apr 4 '06 #2

apattin

Thanks Serge.

I also found another way to do it, in case someone is interested:

SELECT cat1, cat2, CASE subset WHEN 'X' THEN
CASE cat3 WHEN 'Primary' THEN
sum(number) ELSE null END END ,
CASE subset WHEN 'X' THEN
CASE cat3 WHEN 'Secondary' THEN
sum(number) ELSE null END END,
CASE subset WHEN 'X' THEN
CASE WHEN cat3 is null THEN
sum(number) ELSE null END END,
CASE subset WHEN 'Y' THEN
CASE cat3 WHEN 'Primary' THEN
sum(number) ELSE null END END ,
CASE subset WHEN 'Y' THEN
CASE cat3 WHEN 'Secondary' THEN
sum(number) ELSE null END END,
CASE subset WHEN 'Y' THEN
CASE WHEN cat3 is null THEN
sum(number) ELSE null END END
FROM my_table group by cat1, cat2, subset, cat3;

Apr 5 '06 #3