Binaryworld - Rotate a Table in SQL Server ... [ SQL Server -> T-SQL ]

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.

Sample Query to Rotate the Table

Here is the query that you would use to rotate the table:

Click here to copy the following block

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

Query for Large Tables

For large tables, this query will be faster:

Click here to copy the following block

Select
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