DYNAMIC PIVOT


My previous PIVOT UNPIVOT post describes to organize data in a cross tab fashion or to transpose columns into rows and vice-versa.

This is fine only when you know how many columns you need. But when columns vary according to the query then how could you apply them on your scripts. The only way is to store them in a string variable at runtime and apply them in a dynamic SQL query, shown below.

This problem was also discussed on MSDN’s following link:http://social.msdn.microsoft.com/Forums/en/transactsql/thread/26f86fd6-6d06-4cc5-9723-9ee4685bd48a where I proposed an answer also described below:

USE [tempdb]
GO

-- Create test tables
create table #studMarks (
studId int,
subjekt varchar(20),
marks int
)

-- Insert test data
insert into #studMarks values (1, 'math', 90)
insert into #studMarks values (1, 'science', 80)
insert into #studMarks values (1, 'english', 70)
insert into #studMarks values (1, 'history', 60)

insert into #studMarks values (2, 'math', 80)
insert into #studMarks values (2, 'science', 60)
insert into #studMarks values (2, 'english', 90)
insert into #studMarks values (2, 'history', 70)

-- Static PIVOT
select StudId, [math], [science], [english], [history]
from (select StudId, subjekt, marks
from #StudMarks)p
PIVOT(MAX (marks) FOR subjekt IN ([math], [science], [english], [history])
) AS pvt
ORDER BY StudId

Output:

StudId math science english history
1 90 80 70 60
2 80 60 90 70

 

- Dynamic PIVOT:

-- Lets add one more record on both the tables to check the results
insert into #studMarks values (1, 'economics', 95)
insert into #studMarks values (2, 'economics', 85)

declare @col varchar(1000)
declare @sql varchar(2000)

select @col = COALESCE(@col + ', ','') + QUOTENAME(subjekt)
from (select distinct subjekt as subjekt from #StudMarks) K

--select @col

set @sql = '
select StudId, ' + @col + '
from (select StudId, subjekt, marks
from #StudMarks)p
PIVOT(MAX (marks) FOR subjekt IN ( ' + @col + ' )
) AS pvt
ORDER BY StudId'

-- print @sql

exec (@sql)

Output:

StudId economics english history math science
1 95 70 60 90 80
2 85 90 70 80 60

So, whenever any new subject & marks are added in the table against any student the above Dynamic PIVOT statement will take care of it and add a new column for it.

 

- Final Cleanup

drop table #studMarks

 

Source: from my personal blog SQLwithManoj: http://sqlwithmanoj.wordpress.com/2011/01/25/dynamic-pivot/

Skip to main content