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.
You’d like to return a set like the following:
(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.
ID, tblPivot.Property, tblPivot.Value
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
ID, 'FirstName' AS [Property], CONVERT(sql_variant, FirstName) AS [Value]
FirstName IS NOT NULL
ID, 'LastName', CONVERT(sql_variant, LastName)
LastName IS NOT NULL
ID, 'Age', CONVERT(sql_variant, Age)
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+']) FROM ['+@table+'] WHERE ['+COLUMN_NAME+'] IS NOT NULL '
TABLE_NAME = @table
AND COLUMN_NAME <> @id_field
ORDER BY COLUMN_NAME
IF (LEN(@sql) >0)
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.