Tuesday, May 5, 2009

Stored Procedure to Unpivot a Table

PivotWhen 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
*/

1 comment:

  1. The most important thing that you need to be aware if you want your computer work without any inconvenient is the quality of the software which is incorporated in your pc. once i was looking information about new software and i found a site called costa rica investment opportunities it was very interesting.

    ReplyDelete