DYNAMIC PIVOT

--USE [tempdb]
--GO
-- Create test tablescreate table table1

(number int, [desc] varchar(20),location int, numberatlocation
int)create table table2 (code int, name varchar(20))-- Insert test datainsert 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 PIVOTselect number, [desc], [loc1], [loc2], [loc3], [loc4], [loc5]from (select number, [desc], numberatlocation, namefrom table1 join table2 on table1.location=table2.code)pPIVOT(MAX (numberatlocation) FOR Name IN ( [loc1], [loc2], [loc3], [loc4], [loc5] ))
AS pvtORDER BY number
http://sqlwithmanoj.wordpress.com/2011/01/25/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]

02

GO

03

04	-- Create test tables

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

06	location int, numberatlocation int)

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

08

09	-- Insert test data

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

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

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

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

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

15

16	insert into table2 values (1000,'loc1')

17	insert into table2 values (1001,'loc2')

18	insert into table2 values (1002,'loc3')

19	insert into table2 values (1003,'loc4')

20	insert into table2 values (1004,'loc5')

21

22	-- Static PIVOT

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

24	from (select number, desc, numberatlocation, name

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

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

27

) AS pvt

28	ORDER BY number

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

view source

print?

01	-- Dynamic PIVOT

02

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

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

05	insert into table2 values (1005,'loc6')

06

07	declare @col varchar(1000)

08	declare @sql varchar(2000)

09

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

11	from table2

12

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

14

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

16	set @sql = '

17	select number, desc, ' + @col + '

18	from (select number, desc, numberatlocation, name

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

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

21

) AS pvt

22	ORDER BY number'

23

24	print @sql

25

26	exec (@sql)

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

view source

print?

1	-- Final Cleanup

2	drop table table1

3	drop table table2

About these

1	-- Final Cleanup

2	drop table table1

3	drop table table2