a simple but complex query
hai everybody,
consider a table which has column names as col1,col2,col3
tht is
col1 col2 col3 -- column name
xx 10 a1
xx 10 a1
yy 20 a1
yy 20 a1
and the output should be in the following format
col3 xx yy -- column name
a1 20 40
Software/Hardware used:
Software/Hardware used:
ASKED:
December 9, 2005 1:06 AM
UPDATED:
December 11, 2005 5:38 PM
Answer Wiki:
I haven't tested this so it may not be perfect, it's just off the top of my head.
If you are using Oracle the Decode function will help you out, otherwise a case statement should do the trick for most other db's. I am not sure if there is a way you can do this more dynamically ie not specify 'XX' and 'YY'. But this is probably the easiest way I know of.
SELECT
myResults.col3 as col3,
sum(CASE WHEN myResults.col1 = 'XX' then myResults.col2 ELSE 0 END) as 'XX',
sum(CASE WHEN myResults.col1 = 'YY' then myResults.col2 ELSE 0 END) as 'YY'
FROM
(
SELECT myTable.col1 as col1
,sum(myTable.col2) as col2
,myTable.col3 as col3
FROM myTable
GROUP BY myTable.col1, myTable.col3
) myResults
GROUP BY
myResults.col3
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
