Pivoting in SQL

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


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

insert into #column

insert into #data

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
(max(dat) for cname in(‘+@cols+’))p’

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




This entry was posted in SQL. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s