pivot table

One of the most requested pieces of code on the Internet forums about SQL Server these days, is the code for making a crosstab query. There was no native support for that in version 6.5. Not even version 7.0 or version 2000 had this support.
So when Microsoft announced that SQL 2005 would support crosstab queries we all (well, at least me) cheered and anticipated that this should solve a number of difficulties. Many of us have worked with MS Access since version 2.0, and in this application, pivot tables are breazes.



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','one' union all
select '2','two' union all
select '3','three' union all
select '4','four'
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 lakshmi 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 lakshmi
pivot
(
min(Marks) for rn in ([1],[2],[3])
)
pvt
order by id