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 too 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
