If you’ve ever worked with or researched SQL Server’s PIVOT function, you probably noticed most of the samples pivot against an id column. Typically an int column like EmployeeID, or StoreID. That’s fine and dandy, but what happens when you want to PIVOT against a varchar column? If you’ve been in this need you know this is a bit of a task.
I had this need on an app recently and built a little dynamic sql action that does just this. The example below however, uses the the DatabaseLog table in the AdventureWorks sample database to return a count of Events logged for each Schema. Before jumping into the PIVOT, here’s a simple query that gives you the same information, all Schemas, Events, and Event counts.
SELECT [Schema], [Event], COUNT( [Event] ) AS 'event_count' FROM DatabaseLog GROUP BY [Schema], [Event] ORDER BY [Schema]
Running this query should give you a long result looking something like this.
While this query returns the same information to you, I don’t like this format as much as using PIVOT. This query result is long and requires a bit of manipulation to get into a readable format.
Now let’s have a look at retrieving the same information using the PIVOT function.
/*
Example of a dynamic PIVOT against a varchar column from the Adventureworks database
References :
PIVOT & UNPIVOT function
http://msdn.microsoft.com/en-us/library/ms177410.aspx
AdventureWorks sample Databases
http://msdn.microsoft.com/en-us/library/ms124501(v=SQL.100).aspx
AdventreWorks.DatabaseLog
http://msdn.microsoft.com/en-us/library/ms124872.aspx
*/
USE AdventureWorks
-- populate temp Event table
SELECT DISTINCT [Event] as 'Event'
INTO #events
FROM DatabaseLog
-- this var will hold a comma delimited list of [Event]
DECLARE @eventList nvarchar(max)
-- create a flattened [Event], list for the PIVOT statement
SELECT @eventList = COALESCE( @eventList + ', ', '') + CAST( QUOTENAME( [Event] ) AS VARCHAR(1000) )
FROM #events
ORDER BY [Event]
-- drop table var since our data now lives in @eventList
DROP TABLE #events
-- this var will hold the dynamic PIVOT sql
DECLARE @pvt_sql nvarchar(max)
-- NOTE : we're using dynamic sql here because PIVOT
-- does not support sub SELECT in the 'FOR Event IN ( )'
-- part of the query.
-- If we don't use dynamic SQL here, the PIVOT function
-- requires you to hard code each 'Event'
-- Using SELECT * here so the [Event] columns are auto included
SET @pvt_sql = 'SELECT *
FROM
(
SELECT [Event], [Schema]
FROM DatabaseLog
) AS data
PIVOT
(
COUNT( Event )
FOR Event IN
( ' + @eventList + ' )
) AS pvt'
-- run the query
EXEC sp_executesql @pvt_sql
Assuming you have the AdventureWorks database installed on your server, running this sql should give you a result looking something like this.
Show all Schemas and count of each Event type
This query result was truncated to fit in this post, but just know the query above creates a column for every Event in the Databaselog table.
A quick explanation of what’s happening in this sql
- First you fill a table variable ( #events ) with all Events from DatabaseLog
- Next create a comma delimited list of the Events inside of the table variable
- Drop the table variable now that we’ve got our delimited list of Events
- Build the PIVOT statement as a string so you can inject the Events list
- Fire the dynamic SQL via EXEC
Dynamic SQL is something that comes in handy from time to time, but I do my best to only use it if I absolutely have to. In this case we’re using it because the PIVOT function does not allow sub SELECT statements. This is also why we create a specially formatted delimited list of Events prior to building the dynamic sql.
So there you have it, one example of using PIVOT against a varchar column instead of an integer column. Also, this is a pretty good example of a dynamic PIVOT since it’s pretty simple. I hope this makes sense, and if you have any suggestions of better techniques, I’d love to hear it.
