SQL Server 2005 – UNPIVOT

The other day, working on something crazy, I came across a use for the new UNPIVOT keyword in SQL Server 2005. Now, I figured I would use PIVOT before UNPIVOT, but so it goes.

Basically I had a temp table I populated that had some columns, then columns numbered 1-10 with different values. But I needed them to be row based, not column based. I could write some crazy union’s or something, but I figured, it was set up like a pivot table, so why not try UNPIVOT.

SELECT  
    MyId,
    MyName AS ‘Name’,
    tblPivot.Property AS ‘MyProperty’,   
    tblPivot.Value AS ‘MyValue’
INTO #tmp_values
FROM  
  (SELECT MyId, MyName,
CONVERT(sql_variant,[1]) AS [1],
CONVERT(sql_variant,[2]) AS [2],
CONVERT(sql_variant,[3]) AS [3],
CONVERT(sql_variant,[4]) AS [4],
CONVERT(sql_variant,[5]) AS [5],
CONVERT(sql_variant,[6]) AS [6],
CONVERT(sql_variant,[7]) AS [7],
CONVERT(sql_variant,[8]) AS [8],
CONVERT(sql_variant,[9]) AS [9],
CONVERT(sql_variant,[10]) AS [10]
   FROM dbo.MyTable
  UNPIVOT (
    Value For Property In (
    [1],[2],[3],[4],[5],
    [6],[7],[8],[9],[10])
    ) as tblPivot

you can see, you have to make sure all of the fields are the same data type, basically you can get them from being columns to being back to rows with columns Property (would be like 1-10) and then the Value would be the value in the field. Pretty nice and slick!