Unpivoting Data

Unpivoting data is a very useful technique for normalizing denormalized tables. It refers to the process of transposing multiple columns across into rows in a single column. In other words, taking the data below:

sales_year  first_quarter  second_quarter   third_quarter   fourth_quarter
----------  -------------  --------------   -------------   --------------
2006        211203.50      381594.95        439187.00       503155.80
2007        231205.10      451101.25        601209.40       531907.30

and converting to look like this:

sales_year   sales_quarter  sales_amount
-----------  -------------  ------------
2006         1              211203.50
2006         2              381594.95
2006         3              439187.00
2006         4              503155.80
2007         1              231205.10
2007         2              451101.25
2007         3              601209.40
2007         4              531907.30

Here are different methods to achieve that:

-- Create sample table
CREATE TABLE QuarterlySales (
 sales_year INT PRIMARY KEY,
 first_quarter DECIMAL(10, 2),
 second_quarter DECIMAL(10, 2),
 third_quarter DECIMAL(10, 2),
 fourth_quarter DECIMAL(10, 2));
 
-- Insert data
INSERT INTO QuarterlySales VALUES(2006, 211203.50, 381594.95, 439187.00, 503155.80); 
INSERT INTO QuarterlySales VALUES(2007, 231205.10, 451101.25, 601209.40, 531907.30);
 
-- Method 1) Using UNION
SELECT sales_year, 
       1 AS sales_quarter, 
       first_quarter AS sales_amount
FROM QuarterlySales
UNION ALL
SELECT sales_year, 2, second_quarter
FROM QuarterlySales
UNION ALL
SELECT sales_year, 3, third_quarter
FROM QuarterlySales 
UNION ALL
SELECT sales_year, 4, fourth_quarter
FROM QuarterlySales
ORDER BY sales_year, sales_quarter;
 
-- Method 2) Using cross join with table with numbers
SELECT sales_year,
       qtr AS sales_quarter,
       CASE qtr 
         WHEN 1 THEN first_quarter
         WHEN 2 THEN second_quarter
         WHEN 3 THEN third_quarter
         WHEN 4 THEN fourth_quarter
       END AS sales_amount
FROM QuarterlySales
CROSS JOIN
(SELECT 1 UNION ALL
 SELECT 2 UNION ALL
 SELECT 3 UNION ALL
 SELECT 4) AS Quarters(qtr)
ORDER BY sales_year, sales_quarter;
 
-- Method 3) Using the UNPIVOT operator in SQL Server 2005
SELECT sales_year,
       CASE sales_quarter
         WHEN 'first_quarter' THEN 1
         WHEN 'second_quarter' THEN 2
         WHEN 'third_quarter' THEN 3
         WHEN 'fourth_quarter' THEN 4
       END AS sales_quarter,
       sales_amount     
FROM QuarterlySales
UNPIVOT
(sales_amount FOR 
 sales_quarter IN 
 (first_quarter, second_quarter, 
  third_quarter, fourth_quarter)) AS U
ORDER BY sales_year, sales_quarter;

It is good no note that unpivoting multiple columns (like amount1, date1, amount2, date2 to amount and date columns) is not supported by the SQL Server 2005 UNPIVOT operator. This can be achieved only by using the first two methods.

Additional Resources:

Using PIVOT and UNPIVOT
http://msdn2.microsoft.com/en-us/library/ms177410.aspx