By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,018 Members | 901 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,018 IT Pros & Developers. It's quick & easy.

SQL issue? Can this be done?

P: n/a
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
Share this Question
Share on Google+
2 Replies


P: n/a
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

P: n/a
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.