Column To Row (UNPIVOT)
From Wiki
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
- CREATE TABLE #SomeTable
- (SomeID INT,Scarface INT,LOTR INT,Jaws INT,Blade INT,Saw INT)
- INSERT INTO #SomeTable VALUES (1,2,5,2,5,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
- SELECT SomeID,'ScarFace' AS Movie,ScarFace AS Quantity
- FROM #SomeTable
- UNION ALL
- SELECT SomeID,'LOTR' AS Movie,LOTR AS Quantity
- FROM #SomeTable
- UNION ALL
- SELECT SomeID,'Jaws' AS Movie,Jaws AS Quantity
- FROM #SomeTable
- UNION ALL
- SELECT SomeID,'Blade' AS Movie,Blade AS Quantity
- FROM #SomeTable
- UNION ALL
- SELECT SomeID,'Saw' AS Movie,Saw AS Quantity
- FROM #SomeTable
- ORDER BY Movie,SomeID
SQL 2005/2008 version by using UNPIVOT
- SELECT SomeID, Movie, Quantity
- FROM
- (SELECT SomeID, Scarface, LOTR, Jaws, Saw,Blade
- FROM #SomeTable) t
- UNPIVOT
- (Quantity FOR Movie IN
- (Scarface, LOTR, Jaws, Saw,Blade)
- ) AS u
- 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
