INF: How to Rotate a Table in SQL Server

Last reviewed: October 24, 1997
Article ID: Q175574

The information in this article applies to:

Microsoft SQL Server versions 6.0 and 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

Additional query words: crosstab pivot
Keywords : SSrvGen kbusage
Version : WINNT:6.0 6.5
Platform : winnt
Issue type : kbinfo
Solution Type : Info_Provided

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: October 24, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.