SQL Query - convert multiple rows and columns into single row

Question

SQL Query - convert multiple rows and columns into single row

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

pmadhavapeddi22 SSCertifiable Points: 5306 More actions · Answer 1

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 🙂

pmadhavapeddi22 SSCertifiable Points: 5306 More actions · Answer 2

thava (4/25/2014)
hi there, it is almost same with Stuart Davies's but the logic is different
my 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

TomYum Say Hey Kid Points: 704 More actions · Answer 3

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.

David Burrows SSC Guru Points: 65138 More actions · Answer 4

You're welcome 😀

Far away is close at hand in the images of elsewhere.
Anon.