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.
Row To Column (PIVOT)
From Wiki
A very frequent request is how to pivot/transpose/crosstab a query. SQL server 2005 introduced PIVOT, this makes life a lot easier compared to the SQL 2000 days. So let's see how this works. First create this table
- CREATE TABLE #SomeTable
- (SomeName VARCHAR(49), Quantity INT)
- INSERT INTO #SomeTable VALUES ('Scarface', 2)
- INSERT INTO #SomeTable VALUES ('Scarface', 4)
- INSERT INTO #SomeTable VALUES ('LOTR', 5)
- INSERT INTO #SomeTable VALUES ('LOTR', 6)
- INSERT INTO #SomeTable VALUES ( 'Jaws', 2)
- INSERT INTO #SomeTable VALUES ('Blade', 5)
- INSERT INTO #SomeTable VALUES ('Saw', 6)
- INSERT INTO #SomeTable VALUES ( 'Saw', 2)
- INSERT INTO #SomeTable VALUES ( 'Jaws', 12)
- INSERT INTO #SomeTable VALUES ('Blade', 5)
- INSERT INTO #SomeTable VALUES ('Saw', 6)
- INSERT INTO #SomeTable VALUES ( 'Saw', 2)
What we want is to list all the movies in a column and the sum of all quantities for that movie as a value. So in this case we want this output
| Scarface | LOTR | Jaws | Saw | Blade |
| 6 | 11 | 14 | 16 | 10 |
First let's look how we can do this in SQL Server 2000, this BTW will also work in SQL Server 2005/2008:
- SELECT SUM(CASE SomeName WHEN 'Scarface' THEN Quantity ELSE 0 END) AS Scarface,
- SUM(CASE SomeName WHEN 'LOTR' THEN Quantity ELSE 0 END) AS LOTR,
- SUM(CASE SomeName WHEN 'Jaws' THEN Quantity ELSE 0 END) AS Jaws,
- SUM(CASE SomeName WHEN 'Saw' THEN Quantity ELSE 0 END) AS Saw,
- SUM(CASE SomeName WHEN 'Blade' THEN Quantity ELSE 0 END) AS Blade
- FROM #SomeTable
In SQL Server 2005/2008 we can use PIVOT, here is how we can use it
- SELECT Scarface, LOTR, Jaws, Saw,Blade
- FROM
- (SELECT SomeName,Quantity
- FROM #SomeTable) AS pivTemp
- PIVOT
- ( SUM(Quantity)
- FOR SomeName IN (Scarface, LOTR, Jaws, Saw,Blade)
- ) AS pivTable
So that looks a little bit neater than the SQL 2000 version.
Contributed by: --SQLDenis 02:23, 31 May 2008 (GMT)
Part of SQL Server Programming Hacks
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.