Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

Community Wiki

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. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

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.

Navigation

Google Ads

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)
  3.  
  4.  
  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
  3. UNION ALL
  4. SELECT SomeID,'LOTR' AS Movie,LOTR AS Quantity
  5. FROM #SomeTable
  6. UNION ALL
  7. SELECT SomeID,'Jaws' AS Movie,Jaws AS Quantity
  8. FROM #SomeTable
  9. UNION ALL
  10. SELECT SomeID,'Blade' AS Movie,Blade AS Quantity
  11. FROM #SomeTable
  12. UNION ALL
  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
  5. UNPIVOT
  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.8/5 (40 votes cast)