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

Row To Column (PIVOT)

From Wiki

Jump to: navigation, search

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

  1. CREATE TABLE #SomeTable
  2. (SomeName VARCHAR(49), Quantity INT)
  3.  
  4.  
  5. INSERT INTO #SomeTable VALUES ('Scarface', 2)
  6. INSERT INTO #SomeTable VALUES ('Scarface', 4)
  7. INSERT INTO #SomeTable VALUES ('LOTR', 5)
  8. INSERT INTO #SomeTable VALUES ('LOTR', 6)
  9. INSERT INTO #SomeTable VALUES ( 'Jaws', 2)
  10. INSERT INTO #SomeTable VALUES ('Blade', 5)
  11. INSERT INTO #SomeTable VALUES ('Saw', 6)
  12. INSERT INTO #SomeTable VALUES ( 'Saw', 2)
  13. INSERT INTO #SomeTable VALUES ( 'Jaws', 12)
  14. INSERT INTO #SomeTable VALUES ('Blade', 5)
  15. INSERT INTO #SomeTable VALUES ('Saw', 6)
  16. 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:

  1. SELECT SUM(CASE SomeName WHEN 'Scarface' THEN Quantity ELSE 0 END) AS Scarface,
  2. SUM(CASE SomeName WHEN 'LOTR' THEN Quantity ELSE 0 END) AS LOTR,
  3. SUM(CASE SomeName WHEN 'Jaws' THEN Quantity ELSE 0 END) AS Jaws,
  4. SUM(CASE SomeName WHEN 'Saw' THEN Quantity ELSE 0 END) AS Saw,
  5. SUM(CASE SomeName WHEN 'Blade' THEN Quantity ELSE 0 END) AS Blade
  6. FROM #SomeTable


In SQL Server 2005/2008 we can use PIVOT, here is how we can use it

  1. SELECT Scarface, LOTR, Jaws, Saw,Blade
  2. FROM
  3. (SELECT SomeName,Quantity
  4. FROM #SomeTable) AS pivTemp
  5. PIVOT
  6. (   SUM(Quantity)
  7.     FOR SomeName IN (Scarface, LOTR, Jaws, Saw,Blade)
  8. ) 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

Section Sorting, Limiting Ranking, Transposing and Pivoting

279 Rating: 3.2/5 (62 votes cast)