UNPIVOT a Table

The example below illustrates how to UNPIVOT a table.  The example can be used in both SQL Server 2000 and 2005.

CREATE TABLE dbo.TableToUnPivot
(
SalesYear INT,
Q1 INT,
Q2 INT,
Q3 INT,
Q4 INT
)

INSERT dbo.TableToUnPivot SELECT 2003, 1, 2, 3, 4
INSERT dbo.TableToUnPivot SELECT 2004, 5, 6, 7, 8
INSERT dbo.TableToUnPivot SELECT 2005, 9, 10, 11, 12

SELECT  *
FROM  dbo.TableToUnPivot

Returns:

SalesYear   Q1          Q2          Q3          Q4
———– ———– ———– ———– ———–
2003        1           2           3           4
2004        5           6           7           8
2005        9           10          11          12

SELECT SalesYear, T1.Nums AS Quarter,
CASE T1.nums
WHEN 1 THEN Q1
WHEN 2 THEN Q2
WHEN 3 THEN Q3
WHEN 4 THEN Q4
ELSE NULL
END AS UnPivotedData
FROM TableToUnPivot
CROSS JOIN
(
SELECT 1
UNION
SELECT 2
UNION
SELECT 3
UNION
SELECT 4
) T1 (nums)
ORDER BY SalesYear, Quarter

Returns:

SalesYear   Quarter     UnPivotedData
———– ———– ————-
2003        1           1
…
2005        1           9
2005        2           10
2005        3           11
2005        4           12

(12 row(s) affected)