Unpivoting is the process of normalizing data. In earlier post I discussed unpivoting a single column. This demonstration is to show how to unpivot multiple columns.

The task is to normalize the following denormalized data, which represents product sales volumes by quarter:

product_nbr qtr1   sales_qtr1  qtr2   sales_qtr2  qtr3   sales_qtr3  qtr4   sales_qtr4

----------- ------ ----------- ------ ----------- ------ ----------- ------ -----------

1           2008Q1 100         2008Q2 20          2008Q3 15          2008Q4 10

2           2008Q1 80          2008Q2 15          2008Q3 20          2008Q4 10

3           2008Q1 70          2008Q2 5           2008Q3 10          2008Q4 15

Normalized data set should look like this:

product_nbr qtr    sales

----------- ------ -----------

1           2008Q1 100

1           2008Q2 20

1           2008Q3 15

1           2008Q4 10

2           2008Q1 80

2           2008Q2 15

2           2008Q3 20

2           2008Q4 10

3           2008Q1 70

3           2008Q2 5

3           2008Q3 10

3           2008Q4 15

The first method uses CROSS JOIN with table with numbers (needs one number for each quarter) and CASE expressions to select the appropriate value (quarter or sales volume) for each quarter.

SELECT product_nbr, 

       CASE n

            WHEN 1 THEN qtr1

            WHEN 2 THEN qtr2

            WHEN 3 THEN qtr3

            WHEN 4 THEN qtr4

       END AS qtr,

       CASE n

            WHEN 1 THEN sales_qtr1

            WHEN 2 THEN sales_qtr2

            WHEN 3 THEN sales_qtr3

            WHEN 4 THEN sales_qtr4

       END AS sales

FROM QuarterlySales AS S

CROSS JOIN 

(SELECT 1 UNION 

 SELECT 2 UNION

 SELECT 3 UNION

 SELECT 4) AS Nums(n);

Alsmost seems natural that we should be able to do the same much easier using the UNPIVOT operator (introduced in SQL Server 2005). However, one of the limitations of the UNPIVOT operator is that it works only with a single column. But because SQL Server allows multiple table operators in the FROM clause, we can use two UNPIVOT operators. The catch is that the second UNPIVOT operator applies to the virtual table results from the first unpivot operator. That requires using a little trick to extract and match the quarter from the results of each UNPIVOT operator in order to produce the final result. Here is the query to unpivot using the UNPIVOT operator.

SELECT product_nbr, qtr, sales

FROM 

 (SELECT product_nbr, 

         qtr1, sales_qtr1, 

         qtr2, sales_qtr2, 

         qtr3, sales_qtr3,

         qtr4, sales_qtr4

  FROM QuarterlySales) AS S

UNPIVOT

 (qtr FOR qtrx IN (qtr1, qtr2, qtr3, qtr4)) AS U1

UNPIVOT

 (sales FOR sales_qtrx IN (sales_qtr1, sales_qtr2, 

                           sales_qtr3, sales_qtr4)) AS U2

WHERE RIGHT(sales_qtrx, 1) = RIGHT(qtrx, 1);