The mother of all dynamic SQL Crosstab View Builders.

Posted by smeans on February 3, 2009 · Leave a Comment 

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.

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

Filed under coding, database, life · Tagged with , ,