Convert Rows to Columns with aggregate functions

In todays post i would like to show you a simple way to convert rows into columns with aggregate functions. You can also use PIVOT feature to do the same.

create table #Marks
(ID int, Sub int, Value Int)

Insert into #Marks
Select 1, 1, 10 Union all
Select 1, 2, 20 Union all
Select 1, 3, 25 Union all
Select 1, 4, 50 Union all
Select 2, 1, 15 Union all
Select 2, 2, 25 Union all
Select 2, 3, 30 Union all
Select 2, 4, 49

Select * from #Marks;

SELECT Id,
MAX(CASE WHEN Sub = 1 THEN Value ELSE '' END) AS Subject1,
MAX(CASE WHEN Sub = 2 THEN Value ELSE '' END) AS Subject2,
MAX(CASE WHEN Sub = 3 THEN Value ELSE '' END) AS Subject3,
MAX(CASE WHEN Sub = 4 THEN Value ELSE '' END) AS Subject4
FROM #Marks
GROUP BY Id

Thats it :)

Referece: www.DotNetVJ.com