467,209 Members | 1,340 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,209 developers. It's quick & easy.

SQL issue? Can this be done?

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
  • viewed: 979
Share:
2 Replies
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
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

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by George Hester | last post: by
1 post views Thread by Raj Chudasama | last post: by
12 posts views Thread by jburkle | last post: by
5 posts views Thread by Justin | last post: by
1 post views Thread by mthread | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.