Microsoft KB Archive/175574

= How to rotate a table in SQL Server =

Article ID: 175574

Article Last Modified on 12/15/2005

-

APPLIES TO


 * Microsoft SQL Server 6.0 Standard Edition
 * Microsoft SQL Server 6.5 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 2005 Standard Edition
 * Microsoft SQL Server 2005 Developer Edition
 * Microsoft SQL Server 2005 Enterprise Edition
 * Microsoft SQL Server 2005 Express Edition
 * Microsoft SQL Server 2005 Workgroup Edition

-



This article was previously published under Q175574



SUMMARY
 * Sample Query to Rotate the Table
 * Query for Large Tables

REFERENCES



SUMMARY
This article describes how to rotate a SQL Server table. Suppose you have a table that is named QTRSALES. The table has 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 so that you can 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 query that you would use to rotate the table is in the next section of this article.

back to the top

Sample Query to Rotate the Table
Here is the query that 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

back to the top

Query for Large Tables
For large tables, this query will 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

back to the top

