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)
SELECT * FROM #data

/***************************************************
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
*******************************/

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

PRINT @sql
EXEC (@sql)

Advertisements

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: