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:
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;