[T-SQL] Basic Pivot automation script

There's a guy out there who has a great dynamic pivot CLR.  This is a much simpler version.  I _think_ this is original to me.  I've been using it in various projects on this blog, but I don't see where I posted just this code.

Say you have records from your monitor that happens to Track EventLogs.
We'll call it EventLog_Tracking for argument's sake (hint hint http://thebakingdba.blogspot.com/2015/05/powershell-eventlogtracking-capturing.html) and want to look at trends over time.

First, you'd need a grouped set.

SELECT sourcename, CONVERT(DATE, TimeGenerated) AS the_date, COUNT(*) AS the_count
INTO #temp
FROM EventLog_201512
WHERE sourcename LIKE 'microsoft-windows%'
GROUP BY SourceName, CONVERT(DATE,TimeGenerated)
ORDER BY CONVERT(DATE,TimeGenerated), SourceName





And you want it to look like...
 


Here's the base pivot code.  For this example, replace down with "the_date" and across with "sourcename"

If you want a name or something instead of the number, then do something like "max(yourname)" instead of "sum(the_count)"

	DECLARE @SQL as VARCHAR (MAX)
	DECLARE @Columns AS VARCHAR (MAX)
	SELECT @Columns=
	COALESCE(@Columns + ',','') + QUOTENAME(across)
	FROM
	(
	SELECT DISTINCT across
	From #TEMP
	) AS B
	ORDER BY B.across
	SET @SQL='
	SELECT down, ' + @Columns + ' FROM
	(
	SELECT down, across, the_count FROM
	#TEMP ) AS source
	PIVOT
	(SUM(the_count) FOR source.across IN (' + @columns + ')
	)AS pvt'
	EXEC (@sql)

view raw dynamic_pivot.sql hosted with ❤ by GitHub