Pivoting in SQL

This post has good example of using PIVOT to get the row data in column format.

http://beyondrelational.com/modules/2/blogs/88/Posts/14196/changing-rows-to-columns-using-pivot-dynamic-columns-for-pivoting-in-sql-server.aspx

Here is example created by taking reference of this.

create table #table(tid int,tname varchar(10))
create table #column(cid int,tid int,cname varchar(10))
create table #data(tid int,cid int,dat varchar(10))

insert into #table
values(1,’t1′),(2,’t2′)

insert into #column
values(1,1,’c1′),(2,1,’c2′),(3,1,’c3′)

insert into #data
values(1,1,’ss’),(1,2,’pp’),(1,3,’aaa’)

declare @Cols nvarchar(max), @SQL nvarchar(max)

SELECT @cols = STUFF ( (SELECT ‘,’ + cname
FROM #column t1
FOR XML PATH(”)), 1, 1, ”)

set @sql= ‘select * from(select cname,dat from #data d inner join #column c on d.cid=c.cid)pp
pivot
(max(dat) for cname in(‘+@cols+’))p’
exec(@sql)

drop table #table
drop table #column
drop table #data