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!

Technorati tags: , ,
Advertisements

4 thoughts on “SQL Server 2005 – UNPIVOT”

    1. Yes, I did. Might be mangled because of formatting of the post or just a flub copying it to the post. Apostrophes need to be replaced when you try to use it, I wasn’t using a code embed back at this time. Are you sure you are on SQL 2005/2008? It doesn’t work on 2000

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s