SQL Query - convert multiple rows and columns into single row
-
David Burrows (4/25/2014)
SELECT
'Type'[Type]
,SUM(CASE WHEN code='09' THEN Amt/100 ELSE 0 END) AS [Col1]
,SUM(CASE WHEN code='10' THEN Amt/100 ELSE 0 END) AS [Col2]
,SUM(CASE WHEN code='11' THEN Amt/100 ELSE 0 END) AS [Col3]
,SUM(CASE WHEN code='12' THEN Amt/100 ELSE 0 END) AS [Col4]
FROM TEST WHERE (Code BETWEEN '09' AND '12')
This is nice one
-
thava (4/25/2014)
hi there, it is almost same with Stuart Davies's but the logic is differentmy solution is avoid the summation two times the other solution using two times grouping and summing
if see the execution plan you are able to see it clearly
hey, sorry abt that, i did not see about summation
-
David Burrows (4/25/2014)
SELECT
'Type'[Type]
,SUM(CASE WHEN code='09' THEN Amt/100 ELSE 0 END) AS [Col1]
,SUM(CASE WHEN code='10' THEN Amt/100 ELSE 0 END) AS [Col2]
,SUM(CASE WHEN code='11' THEN Amt/100 ELSE 0 END) AS [Col3]
,SUM(CASE WHEN code='12' THEN Amt/100 ELSE 0 END) AS [Col4]
FROM TEST WHERE (Code BETWEEN '09' AND '12')
Hello David,
Thanks a lot for this solution. I think this one will do the job for me.
Thank you all for you contribution.
-
You're welcome
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 4 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply