DynamicPivot.sql

/*
Example of a dynamic PIVOT against a varchar column from the Adventureworks database

References :

PIVOT & UNPIVOT
http://msdn.microsoft.com/en-us/library/ms177410.aspx

COALESCE
http://msdn.microsoft.com/en-us/library/ms190349.aspx

QUOTENAME
http://msdn.microsoft.com/en-us/library/ms176114.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

-- holds the list of Events to be used column headers below
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