Geeky/Programming SQLServerPedia Syndication

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.

    MyName AS ‘Name’,
    tblPivot.Property AS ‘MyProperty’,   
    tblPivot.Value AS ‘MyValue’
INTO #tmp_values
  (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
    Value For Property In (
    ) 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: , ,

By Steve Novoselac

Director of Digital Technology @TrekBikes, Father, Musician, Cyclist, Homebrewer

4 replies on “SQL Server 2005 – UNPIVOT”

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


Leave a Reply

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

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

Facebook photo

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.