DYNAMIC PIVOT Table in Sql server

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.

USE [tempdb]

GO

 

-- Create test tables

create table table1 (number int, desc varchar(20),

location int, numberatlocation int)

create table table2 (code int, name varchar(20))

 

-- Insert test data

insert into table1 values (12345,'test',1000,5)

insert into table1 values (12345,'test',1001,2)

insert into table1 values (12345,'test',1002,4)

insert into table1 values (12345,'test',1003,9)

insert into table1 values (12345,'test',1004,7)

 

insert into table2 values (1000,'loc1')

insert into table2 values (1001,'loc2')

insert into table2 values (1002,'loc3')

insert into table2 values (1003,'loc4')

insert into table2 values (1004,'loc5')

 

-- Static PIVOT

select number, description, [loc1], [loc2], [loc3], [loc4], [loc5]

from (select number, desc, numberatlocation, name

from table1 join table2 on table1.location=table2.code)p

PIVOT(MAX (numberatlocation) FOR Name IN ( [loc1], [loc2], [loc3], [loc4], [loc5] )

) AS pvt

ORDER BY number

 

 

Output of Static query:
number desc loc1 loc2 loc3 loc4 loc5
12345 test    5       2       4       9       7

-- Dynamic PIVOT

 

-- Lets add one more record on both the tables to check the results

insert into table1 values (12345,'test',1005,3)

insert into table2 values (1005,'loc6')

 

declare @col varchar(1000)

declare @sql varchar(2000)

 

select @col = COALESCE(@col + ', ','') + QUOTENAME(name)

from table2

 

select @col -- This gives: [loc1], [loc2], [loc3], [loc4], [loc5], [loc6]

 

-- Now setting this @col variable in the Dynamic SQL.

set @sql = '

select number, desc, ' + @col + '

from (select number, desc, numberatlocation, name

from table1 join table2 on table1.location=table2.code)p

PIVOT(MAX (numberatlocation) FOR Name IN ( ' + @col + ' )

) AS pvt

ORDER BY number'

 

print @sql

 

exec (@sql)

Output of Dynamic query:
number desc loc1 loc2 loc3 loc4 loc5    loc6
12345 test    5       2       4       9       7       3

-- Final Cleanup

drop table table1

drop table table2