Login or Sign Up to become a member!

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.

Your profile

Search

Views

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

tsqlLine number Off | Hide | Select all

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

tsqlLine number Off | Hide | Select all

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

tsqlLine number Off | Hide | Select all

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

Section Sorting, Limiting Ranking, Transposing and Pivoting

Currently 2.23/5
1
2
3
4
5

280 Rating: 2.2/5 (13 votes cast)

Retrieved from "http://wiki.lessthandot.com/index.php/Column_To_Row_%28UNPIVOT%29"