Pivot Function with dynamic column headings

Problem: You need to use the pivot function but the columns heading are not static

Solution: Produce the pivot select using dynamic sql
IF Object_id(‘tempdb..#data’) IS NOT NULL DROP TABLE #data

CREATE TABLE #data (label VARCHAR(5),value int)
INSERT INTO #data VALUES (‘A’,1),(‘A’,2),(‘B’,1),(‘B’,3),(‘A’,1)

our column headings will come from the values column

Step 1: Create the pivot

DECLARE @pivot varchar(MAX) = (select STUFF ((SELECT distinct ‘, ‘ + ‘[‘+ cast(value as varchar(1)) +’]’ AS ‘data()’
FROM #data
FOR XML PATH (”)) , 1, 1, ”))

print @pivot
Step 2: Create the select string

‘SELECT * FROM (SELECT label,value from #data) as a PIVOT (count(value) for value IN (‘ + @pivot + ‘)) AS Piv’

PRINT @sql
EXEC (@sql)


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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: