T-SQL Challenges - Part 1 -Pivot Operator

Problematic statement:
Consider two table 1 and 2 and they are linked by a common column "ID".
Table1 had the details about the person and table2 list down the scores availed by the person.
My problem is to combine these two table listing out the person and their marks in each subjects.
Assumptions:
In table2, the rows are added as per the subjects, first mark will be considered as subject1,
second mark will be considered as subject2,Thrid mark will be considered as subject3.
Below are the sample inputs and expected outputs,
SQLTable1:

Code Snippet

ID StudentName 1 Venkatesan 2 Arun 3 Santhi 4 Vijay

SQLTable2:

Code Snippet

ID Marks 1 90 1 20 1 80 2 78 2 67 3 89 3 65 3 98 4 78 4 76 4 45

Expected output from the above tables

Code Snippet

ID StudentName Sub1 Sub2 Sub3 --------------------------------------------------------- 1 Venkatesan 90 20 80 2 Arun 78 67 NULL 3 Santhi 89 65 98 4 Vijay 78 76 45

Solution:
Microsoft sql server 2005 provides a very handy option of altering the rows to columns
using pivot property. To solve the above problem,
1. First we have to create a cte listing the rownumber partitioned by ID.
2. Pivot the data based on rownumber.

Code Snippet

--Creating table variables declare @SQLTable1 table (id int,Studentname varchar(100)) declare @SQLTable2 table (id int,Marks int) -- inserting the data into the table variables insert into @SQLTable1 select '1','Venkatesan' union all select '2','Arun' union all select '3','Santhi' union all select '4','Vijay' insert into @SQLTable2 select '1','90' union all select '1','20' union all select '1','80' union all select '2','78' union all select '2','67' union all select '3','89' union all select '3','65' union all select '3','98' union all select '4','78' union all select '4','76' union all select '4','45' --select * from @SQLTable1 --select * from @SQLTable2 --Creating rownumber in the cte ;with VenkatCTE as( select a.id,a.Studentname, b.Marks, row_number() over ( partition by a.id order by a.id) as rn from @SQLTable1 a inner join @SQLTable2 b on a.id=b.id ) --select * from VenkatCTE -- pivoting the rows in the cte select id,Studentname,[1] as Subject1,[2] as Subject2,[3] as Subject3 from VenkatCTE pivot ( min(Marks) for rn in ([1],[2],[3]) ) pvt order by id

Happy learning!!!
Regards,
Venkatesan Prabu . J