Ok, so maybe it’s not too poetic, but it’s descriptive. As I found myself writing a dynamic crosstab-query builder in MS SQL for probably the fifth time of my career, I decided I’d post it here to save you all just a little time in your day. Make sure you use the saved time for something I’d approve of (smoking a good cigar, playing a little poker, something like that).
This proc is designed for when you have a dynamic set of row-oriented data you want to pivot and show column-wise. It takes four parameters:
@tableName – Name of the source table (or view) that will be queried by the new crosstab view.
@excludeCols – A comma separated list of columns to exclude from the crosstab. In most cases, will be the ID column of the table.
@valueColName – Name of the column in the @tableName table that contains the value in a given row.
@targetViewName – Name of the view that the stored procedure will create. It will *automatically* drop this view before re-creating it, so be warned.
This proc also assumes that the @tableName table (or view) has a column called [colName]. It uses this value to create the corresponding columns across the top of the output view.
ALTER PROCEDURE BuildCrosstabView( @tableName VARCHAR(100), @excludeCols VARCHAR(1000), @valueColName VARCHAR(1000), @targetViewName VARCHAR(100) ) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DECLARE @cols VARCHAR(4000) SET @cols = '' EXEC ('SELECT DISTINCT colName INTO ##colList FROM ' + @tableName) DECLARE col_cursor CURSOR FOR SELECT colName FROM ##colList OPEN col_cursor DECLARE @colName VARCHAR(100) FETCH NEXT FROM col_cursor INTO @colName WHILE @@FETCH_STATUS = 0 BEGIN IF @cols <> '' BEGIN SET @cols = @cols + ', ' END SET @cols = @cols + 'MAX(CASE colName WHEN ''' + @colName + ''' THEN ' + @valueColName + ' ELSE NULL END) AS [' + @colName + '] ' FETCH NEXT FROM col_cursor INTO @colName END CLOSE col_cursor DEALLOCATE col_cursor DROP TABLE ##colList DECLARE @sql VARCHAR(3000) SET @sql = 'SELECT DISTINCT ' + @excludeCols + ', ' + @cols + ' FROM ' + @tableName + ' GROUP BY ' + @excludeCols PRINT @sql EXECUTE (@sql) IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[' + @targetViewName + ']')) EXECUTE ('DROP VIEW ' + @targetViewName) EXECUTE ('CREATE VIEW ' + @targetViewName + ' AS ' + @sql) END GO