Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Column To Row (UNPIVOT)

From Wiki

Jump to: navigation, search

Row to column is a much more frequent request than column to row. SQL Server 2005 introduced UNPIVOT to help you accomplish this task. Before SQL Server 2005 the only way was to write a bunch of unioned select statements

Let's take a look at how this all works. create this table and the data

  1. CREATE TABLE #SomeTable
  2. (SomeID INT,Scarface INT,LOTR INT,Jaws INT,Blade INT,Saw INT)
  5. INSERT INTO #SomeTable VALUES (1,2,5,2,5,6)
  6. INSERT INTO #SomeTable VALUES (2,4,6,12,5,0)

This is what we have now

SomeID Scarface LOTR Jaws Saw Blade
1 2 5 2 5 6
2 4 6 12 5 0

The output we need is this

1 Blade 5
2 Blade 5
1 Jaws 2
2 Jaws 12
1 LOTR 5
2 LOTR 6
1 Saw 6
2 Saw 2
1 ScarFace 2
2 ScarFace 4

SQL 2000 version by using union

  1. SELECT SomeID,'ScarFace' AS Movie,ScarFace AS Quantity
  2. FROM #SomeTable
  4. SELECT SomeID,'LOTR' AS Movie,LOTR AS Quantity
  5. FROM #SomeTable
  7. SELECT SomeID,'Jaws' AS Movie,Jaws AS Quantity
  8. FROM #SomeTable
  10. SELECT SomeID,'Blade' AS Movie,Blade AS Quantity
  11. FROM #SomeTable
  13. SELECT SomeID,'Saw' AS Movie,Saw AS Quantity
  14. FROM #SomeTable
  15. ORDER BY Movie,SomeID

SQL 2005/2008 version by using UNPIVOT

  1. SELECT SomeID, Movie, Quantity
  2. FROM
  3. (SELECT SomeID, Scarface, LOTR, Jaws, Saw,Blade
  4.  FROM #SomeTable) t
  6. (Quantity FOR Movie IN
  7.     (Scarface, LOTR, Jaws, Saw,Blade)
  8. ) AS u
  9. ORDER BY Movie,SomeID

Here you can really see that you save a lot of code by using UNPIVOT over UNION

Contributed by: --SQLDenis 02:24, 31 May 2008 (GMT)

Part of SQL Server Programming Hacks

Section Sorting, Limiting Ranking, Transposing and Pivoting

280 Rating: 2.0/5 (126 votes cast)