The mother of all dynamic SQL Crosstab View Builders

SQL_crosstabimgOk, 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.

Good luck!

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

Leave a Reply

Your email address will not be published. Required fields are marked *