INF: How to Rotate a Table in SQL Server

ID: Q175574

The information in this article applies to:

Microsoft SQL Server versions 6.0, 6.5

This article describes how you can rotate a SQL Server table. Suppose you have a table named QTRSALES with the columns YEAR, QUARTER, and AMOUNT with the data in the following format (note that there is no row for the fourth quarter of 1996):


   Year   Quarter       Amount
   -------------------------------
   1995      1          125,000.90
   1995      2          136,000.75
   1995      3          212,000.34
   1995      4          328,000.82
   1996      3          728,000.35
   1996      2          422,000.13
   1996      1          328,000.82

Now suppose you want to rotate the table, to see the data in the following format:


   YEAR        Q1              Q2              Q3              Q4
   -------------------------------------------------------------------
   1995     125,000.90      136,000.75      212,000.34      328,000.82
   1996     328,000.82      422,000.13      728,000.35            0.00

The following is the query you would use to rotate the table:


SELECT YEAR,
       Q1= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 1 AND YEAR =
  Q.YEAR),0),
       Q2= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 2 AND YEAR =
  Q.YEAR),0),
       Q3= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 3 AND YEAR =
  Q.YEAR),0),
       Q4= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 4 AND YEAR =
  Q.YEAR),0)
     FROM QTRSALES Q
     GROUP BY YEAR

For very large tables, the following query should be faster:


year=q.year,
SUM(CASE quarter WHEN 1 THEN amount ELSE 0 END) as Q1,
SUM(CASE quarter WHEN 2 THEN amount ELSE 0 END) as Q2,
SUM(CASE quarter WHEN 3 THEN amount ELSE 0 END) as Q3,
SUM(CASE quarter WHEN 4 THEN amount ELSE 0 END) as Q4
FROM qtrsales q
GROUP BY year

Additional query words: crosstab pivot

Keywords : kbusage SSrvGen
Version : WINNT:6.0 6.5
Platform : winnt
Issue type : kbinfo