Stored Procedure to Unpivot a Table

When I first started blogging, I wrote a post about this 3 liner (well, technically) stored procedure to unpivot data. Well, I was new to blogging and I didn't really have all of the great tools that I have now, so it was a pretty crappy post.

Well, I was just flipping through my archives looking at the lack of SQL Server posts that I have and thought, "I really should rewrite that one and use a good highlighter and post some sample usage."

So, I did, but before I get to the stored procedure, why don't I explain a little bit about what unpivoting is? Sometimes you don't know what kind of data you're going to be collecting so you can't really create a third normal schema to store these data. Instead, you use something called an Attribute Value Pair table (or AVP for short).

This table ends up being tall instead of wide, but you can store any data you please in there. Problem is, how do you get to a full and meaningful record? Well, the process to take tall data and make it wide is called pivoting. If, however, you have wide tables and you need to seed this AVP table, that process is called unpivoting.

I had to do this several times and I didn't really care to write a view every time I had to unpivot, so I wrote a sproc instead. Here's that stored procedure and it should work on all SQL Server 2008 and SQL Server 2005:

CREATE PROCEDURE UnpivotTable
(
  @tableName AS VARCHAR(512),
  @whereClause AS VARCHAR(2000) = NULL,
  @commonColumns AS VARCHAR(2000) = NULL
) AS
-- ==========================================================
-- Author:        Patrick Caldwell
-- Create date:   2007/12/19
-- Description:   this procedure unpivots data in a table
--                specified in the parameters with an optional
--                where clause.  every line has been commented.
--                the commonColumns variable stores a list of
--                columns that should be on every row.
-- ==========================================================


-- a variable in which to store the query
DECLARE @query VARCHAR(MAX);

-- build the query
SELECT            @query = ISNULL(@query + ' ', '') + QueryLine
FROM
(
    SELECT        QueryLine = 'SELECT ' +
                    ISNULL(@commonColumns + ', ', '') +
                    'ColumnName = ''' + COLUMN_NAME + ''', ' +
                    'ColumnValue = CAST([' + COLUMN_NAME + '] AS NVARCHAR(4000)) ' +
                    'FROM ' + @tableName +
                    ISNULL(' WHERE ' + @whereClause, '') +
                    ' UNION ALL'
    FROM          INFORMATION_SCHEMA.COLUMNS
    WHERE         TABLE_SCHEMA + '.' + TABLE_NAME = @tableName

    UNION ALL

    SELECT        'SELECT ' +
                    ISNULL(@commonColumns + ', ', '') +
                    'NULL, NULL ' +
                    'FROM ' + @tableName + ' ' +
                    'WHERE 1 = 0'
) AS lines;

-- execute the query
EXEC(@query);

For the sake of example, I'm going to need a test table. Here's the one I'm using:

CREATE TABLE Pals
(
  Id TINYINT IDENTITY(1, 1), -- so what if I only have 255 pals?
  FirstName VARCHAR(128),
  LastName VARCHAR(128),
  Gender CHAR(1)
);

INSERT INTO Pals VALUES ('Lauren', 'Woody', 'F'); -- no, really . . . F
INSERT INTO Pals VALUES ('James', 'Brechtel', '?');
INSERT INTO Pals VALUES ('Ryan', 'McGarty', 'M');

And finally, here are some example usages:

EXEC UnpivotTable @tableName = 'dbo.pals'
/*
ColumnName ColumnValue
---------- -----------
Id         1
Id         2
Id         3
FirstName  Lauren
FirstName  James
FirstName  Ryan
LastName   Woody
LastName   Brechtel
LastName   McGarty
Gender     F
Gender     ?
Gender     M
*/

EXEC UnpivotTable @tableName = 'dbo.pals', @commonColumns = 'Id'
/*
Id   ColumnName ColumnValue
---- ---------- -----------
1    Id         1
2    Id         2
3    Id         3
1    FirstName  Lauren
2    FirstName  James
3    FirstName  Ryan
1    LastName   Woody
2    LastName   Brechtel
3    LastName   McGarty
1    Gender     F
2    Gender     ?
3    Gender     M
*/

EXEC UnpivotTable @tableName = 'dbo.pals', 
                  @commonColumns = 'Id', 
                  @whereClause = 'Gender = ''F'''
/*
Id   ColumnName ColumnValue
---- ---------- -----------
1    Id         1
1    FirstName  Lauren
1    LastName   Woody
1    Gender     F
*/