UNPIVOT, Normalization, SQL Server 2000 and SQL Server 2005

I’ve come across this question quite a few times.

I have rows with lots of columns, and would like to split it outso that each column is a row

Imagine a scenario where you have a wide table that you’d liketo normalize, at least in view or façade(or perhaps in a data transformation layer) – You’d like toun-pivot it, rotating columns into rows.

For a sample, I’ll use the questioner’s scenario of a Persontable with a variety of attributes. Don’t get hung-up on the tableor whether it’s already normalized (the sample came directly from auser who had a rather unique need), the point is simply rotatingcolumns into rows.

Table: Person

ID FirstName LastName Age
1 Bob Jones 32
2 Jeremy Jones 2
3 Laura Croft 26
4 John Dingbat 22

You’d like to return a set like the following:

ID Property Value
1 FirstName Bob
1 LastName Jones
1 Age 32
2 FirstName Jeremy
2 LastName Jones
2 Age 2

(rows truncated for some brevity)

SQL Server 2005 offers the UNPIVOToperator of the FROM clause, which can made quick(albeit unintuitively and inflexibly) work of this specificneed.

SELECT   
ID, tblPivot.Property, tblPivot.Value
FROM
(SELECT ID,
CONVERT(sql_variant,FirstName) AS FirstName,
CONVERT(sql_variant,LastName) AS LastName,
CONVERT(sql_variant,Age) AS Age
FROM Person) Person
UNPIVOT (Value For Property In (FirstName, LastName, Age)) as tblPivot

The use of the derived table in the FROM clause ispurely to cast the columns to a common data type, as this is arequirement of the UNPIVOT operator. Otherwise all of the sourcecolumns would need to have the same type (precisely the sametype).

Note that the IN list has to be a literal, concrete list – youcan’t pass a table variable or subquery. Always perplexing whenthey limit these sorts of operators in this way.

With SQL Server 2000 you can do this viasimple unions or temporary tables

SELECT 
ID, 'FirstName' AS [Property], CONVERT(sql_variant, FirstName) AS [Value]
FROM
Person
WHERE
FirstName IS NOT NULL

UNION ALL

SELECT
ID, 'LastName', CONVERT(sql_variant, LastName)
FROM
Person
WHERE
LastName IS NOT NULL

UNION ALL
SELECT
ID, 'Age', CONVERT(sql_variant, Age)
FROM
Person
WHERE
Age IS NOT NULL

In this case we’ve made the common typesql_variant, though obviously you should alteraccording to your data. We’ve also decided to eliminate null values(so there aren’t null property rows), though that depends upon theneed.

Another option is to make a programmaticallyflexible alternative that automatically adapts to the schema of thetable (within constraints). For instance consider the followingscript.

SET NOCOUNT ON
DECLARE @table sysname 
SET @table = 'Person'
DECLARE @id_field sysname 
SET @id_field = 'ID'
DECLARE @sql varchar(8000)
-- create the schema of the resulting table 
SET @sql = 'SELECT TOP 0 CONVERT(int,0) AS [ID], '
+'CAST(0 AS nvarchar(4000)) AS [Property],'
+' CONVERT(sql_variant,N'''') AS [Value] WHERE 1=0 '+CHAR(10)
SELECT @sql = @sql + 'UNION ALL SELECT '+@id_field+', N'''
+COLUMN_NAME+''',CONVERT(sql_variant, '
+'['+COLUMN_NAME+']) FROM ['+@table+'] WHERE ['+COLUMN_NAME+'] IS NOT NULL '
+CHAR(10)
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = @table
AND COLUMN_NAME <> @id_field
ORDER BY COLUMN_NAME
IF (LEN(@sql) >0) 
BEGIN
EXEC(@sql)
END

In this case it uses the object schema, though you could alterit to go against a property table or the like.

Standard disclaimers about injection attacks and all of thatapply (presumably you won’t be calling this with untrusted input),and of course it won’t work if you have composite keys, or if youhave so many columns that the resulting SQL exceeds 8000characters. Adapt accordingly. It also does no sorting, so add asyou need it.

Tagged: [], []