Unpivoting Multiple Columns

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 THEN qtr1

            
WHEN THEN qtr2

            
WHEN THEN qtr3

            
WHEN THEN qtr4

       
END AS qtr,

       
CASE n

            
WHEN THEN sales_qtr1

            
WHEN THEN sales_qtr2

            
WHEN THEN sales_qtr3

            
WHEN THEN sales_qtr4

       
END AS sales

FROM QuarterlySales AS S

CROSS JOIN 

(SELECT UNION 

 SELECT 
UNION

 SELECT 
UNION

 SELECT 
4AS 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_nbrqtrsales

FROM 

 
(SELECT product_nbr

         
qtr1sales_qtr1

         
qtr2sales_qtr2

         
qtr3sales_qtr3,

         
qtr4sales_qtr4

  
FROM QuarterlySalesAS S

UNPIVOT

 (qtr FOR qtrx IN (qtr1qtr2qtr3qtr4)) AS U1

UNPIVOT

 (sales FOR sales_qtrx IN (sales_qtr1sales_qtr2

                           
sales_qtr3sales_qtr4)) AS U2

WHERE RIGHT(sales_qtrx1RIGHT(qtrx1);