Dynamic Pivot in TSQL

Making 'dynamic' even more dynamic

In this month’s SQL Server Magazine (11/2009) an article was featured on the concept of dynamic pivot. This was of interest to me because, since I peruse the stats on how people arrive at this site, I have seen several searches from Google! looking for dynamic pivot examples. Truth be told, I haven’t run into a situation to dynamically pivot anything in a production environment, so I’m not sure what all of the drooling is all about. But, since there is a demand, I thought I’d throw my .02 cents in, and I’ll use SQLMag’s example as my base reference.

The author did a good job with the write-up - my hat is off to him. However, glancing at the article one can see that concept could be carried further…very little is “dynamic” about his code, so l invite you to look at it and mine and decide for yourself. First, let’s set up the tables and data. I have included both his [1] DDL as well as that from another blog posting elsewhere on this site (to demonstrate that it will work in a dynamic fashion, all things being equal).

DROP TABLE DemoTable

CREATE TABLE DemoTable

(

AssignmentName VARCHAR(255),

StudentName VARCHAR(255),

Grade INT

)

INSERT INTO DemoTable

Values('Assignment1', 'John Smith', 70)

INSERT INTO DemoTable

Values('Assignment1', 'Jane Smith', 80)

INSERT INTO DemoTable

Values('Assignment1', 'Paul Smith', 75)

INSERT INTO DemoTable

Values('Assignment2', 'John Smith', 50)

INSERT INTO DemoTable

Values('Assignment2', 'Paul Smith', 65)

INSERT INTO DemoTable

Values('Assignment3', 'Jane Smith', 70)

DROP TABLE PlantMetrics;

CREATE TABLE PlantMetrics

(PlantID int

,TransDt datetime

,Metric char (4)

,Value nvarchar (10)

,CONSTRAINT pk_plmt PRIMARY KEY CLUSTERED (PlantID, TransDt, Metric)

);

INSERT PlantMetrics

VALUES (1,'7/1/09', 'MHRS', '24')

,(1,'7/1/09', 'WTHR', 'Sunny')

,(1,'7/1/09', 'TEMP', '88')

,(2,'7/1/09', 'TEMP', '94')

,(3,'7/2/09', 'DOWN', '7')

Below is his code [1]:

DECLARE @SQL as VARCHAR (MAX)

DECLARE @Columns AS VARCHAR (MAX)

SELECT @Columns=

COALESCE(@Columns + ',','') + QUOTENAME(AssignmentName)

FROM

(

SELECT DISTINCT AssignmentName

From DemoTable

) AS B

ORDER BY B.AssignmentName

SET @SQL='

WITH PivotData AS

(

SELECT

AssignmentName,

StudentName,

Grade

FROM DemoTable

)

SELECT

StudentName,

' + @Columns + '

FROM PivotData

PIVOT

(

SUM(Grade)

FOR AssignmentName

IN (' + @Columns + ')

) AS PivotResult

ORDER BY StudentName'

EXEC (@SQL)

A few things to point out: don’t use SUM for the aggregate. MIN or MAX works better, because sometimes the value that is the subject of the pivot is not an integral (integer, numeric, etc.) type. Also, I see some columns that could be replaced here to make the entire code dynamic. Finally, here is my code:

DECLARE @SQL as VARCHAR(MAX)

DECLARE @NewColumnList AS VARCHAR(MAX)

DECLARE @table table (SourceColumnNames varchar (100))

DECLARE @TargetTable varchar (100)

-- Column names

DECLARE @PivotColumn varchar (100)

DECLARE @AnchorColumn varchar (100)

-- Pivot

DECLARE @PivotValue varchar (100)

DECLARE @Operation char (3)

SET @TargetTable = 'DemoTable'

SET @PivotColumn = 'AssignmentName'

SET @AnchorColumn = 'StudentName'

SET @PivotValue = 'Grade'

SET @Operation = 'MAX'

INSERT @table

EXEC ('SELECT ' + @PivotColumn + ' FROM ' + @TargetTable )

SELECT @NewColumnList = COALESCE(@NewColumnList + ', ','') + QUOTENAME(SourceColumnNames)

FROM

(

SELECT DISTINCT SourceColumnNames

FROM @table

) AS B

SET @SQL = '

WITH PivotData AS

(

SELECT

' + @PivotColumn + ',

' + @AnchorColumn + ',

' + @PivotValue + '

FROM ' + @TargetTable+ '

)

SELECT

' + @AnchorColumn + ',

' + @NewColumnList + '

FROM PivotData

PIVOT

(

'+@Operation+'(' + @PivotValue + ')

FOR ' + @PivotColumn + '

IN (' + @NewColumnList + ')

) AS PivotResult

ORDER BY ' + @AnchorColumn + ''

EXEC (@SQL)

You can see that there is nothing terribly difficult or complicated about this. Change up a few things, add a few variables, and we’re set! Notice my naming convention for the variables used to help in understanding this thing. I named these for no reason other than they seemed to make sense to me, considering Books Online didn’t attempt to help out much with the components of the syntax. Here’s the skinny on the var names:

· @TargetTable – the name of the dynamic table

· @PivotColumn –the row name that will be used to generate columns in the pivot

· @AnchorColumn – the column that stays put and is unchanged in its’ purpose in life

· @PivotValue –the metric that in the pivot table

· @Operation – probably shouldn’t have even used this, could have hard coded it MAX or MIN because sometimes values can’t be summed or averaged.

In closing, do we need ‘dynamic’ pivot (or unpivot for that matter)? Not sure, but I know that I haven’t needed it in production for either of my past two clients. Who knows…maybe someone can use something like this; as always let me know.

Happy dynamic pivoting!

Lee

-----------------------------

If I start walking to Las Vegas tonight, I think I can make SQLServerConnections before its over