Unpivot without Unpivot

The standard Unpivot statement in SQL provides an easy and straight-forward way to unpivot columns. However, it only works when there is a single unpivoted column we want.

SELECT * 
FROM   ProductSalesUSD 

Year_Month	Product1_Sales_USD	Product2_Sales_USD	Product3_Sales_USD
201701	35000	9000	19000
201702	36000	8500	18500
201703	34000	13000	19500
201704	35500	12000	18000
201705	36800	10000	18000
201706	36000	15000	19000

-- Unpivot 
SELECT year_month, 
       product, 
       sales 
FROM   (SELECT Year_Month, 
               Product1_Sales_USD AS Product1, 
               Product2_Sales_USD AS Product2, 
               Product3_Sales_USD AS Product3 
        FROM   ProductSalesUSD) PS 
       UNPIVOT (Sales 
               FOR Product IN (Product1, 
                               Product2, 
                               Product3)) AS UP 

Year_Month	Product	Sales
201701	Product1	35000
201701	Product2	9000
201701	Product3	19000
201702	Product1	36000
201702	Product2	8500
201702	Product3	18500
201703	Product1	34000
201703	Product2	13000
201703	Product3	19500
201704	Product1	35500
201704	Product2	12000
201704	Product3	18000
201705	Product1	36800
201705	Product2	10000
201705	Product3	18000
201706	Product1	36000
201706	Product2	15000
201706	Product3	19000

Sometimes it's difficult to remember he syntax for UNPIVOT. Here is how we can try. In the Unpivot section, it is

Metric FOR Category IN (Cat1, Cat2, Cat3)

Where both Metric and Category are arbitrary names chosen to represent respectively the value in the original columns and category which we are unpivoting into.

Now consider an example with more than one type of value for each category. 

SELECT * 
FROM   ProductSales

Year_Month	Prod1_Sales_USD	Prod1_Sales_Fx	Prod2_Sales_USD	Prod2_Sales_Fx	Prod3_Sales_USD	Prod3_Sales_Fx
201701	35000	30800	9000	7920	19000	16720
201702	36000	31680	8500	7480	18500	16280
201703	34000	29920	13000	11440	19500	17160
201704	35500	31240	12000	10560	18000	15840
201705	36800	32384	10000	8800	18000	15840
201706	36000	31680	15000	13200	19000	16720

In this case we have multiple sales columns for each product in our dataset - Sales in USD and Sales in Local currency. Then we need two sales columns when we unpivot the products. This cannot be achieved using the Unpivot statement.  We can use Cross Apply in order to return such a result as below:

SELECT Year_Month, 
       Product, 
       SalesUSD, 
       SalesFx 
FROM   ProductSales PS 
       CROSS APPLY (SELECT Product1_Sales_USD AS SalesUSD, 
                           Product1_sales_Fx  AS SalesFx, 
                           'Product1'         AS Product 
                    UNION ALL 
                    SELECT Product2_Sales_USD AS SalesUSD, 
                           Product2_Sales_Fx  AS SalesFx, 
                           'Product2'         AS Product 
                    UNION ALL 
                    SELECT Product3_Sales_USD AS SalesUSD, 
                           Product3_Sales_Fx  AS SalesFx, 
                           'Product3'         AS Product) UP 

Year_Month	Product	SalesUSD	SalesFx
201701	Product1	35000	30800
201701	Product2	9000	7920
201701	Product3	19000	16720
201702	Product1	36000	31680
201702	Product2	8500	7480
201702	Product3	18500	16280
201703	Product1	34000	29920
201703	Product2	13000	11440
201703	Product3	19500	17160
201704	Product1	35500	31240
201704	Product2	12000	10560
201704	Product3	18000	15840
201705	Product1	36800	32384
201705	Product2	10000	8800
201705	Product3	18000	15840
201706	Product1	36000	31680
201706	Product2	15000	13200
201706	Product3	19000	16720

We can actually use this method to unpivot 1 or 2 or 3 or more value columns. The key thing to remember here is the structure of the right query for Cross Apply. We need one Union block for each category (in this example, Product), and the number of columns in each block is the number of value columns plus one for the hardcoded category name. That is, two sales columns - Sales USD and Sales Local Currency and product name. When we have other sets of product columns in the original dataset corresponding to Profit, Cost etc., we can just extend the current code by adding the columns to each Union block.

So, we can unpivot without having to use the Unpivot keyword. The Cross Apply code for the first example with one Sales column will be:

SELECT Year_Month, 
       Product, 
       SalesUSD
FROM   ProductSales PS 
       CROSS APPLY (SELECT Product1_Sales_USD AS SalesUSD,                         
                           'Product1'         AS Product 
                    UNION ALL 
                    SELECT Product2_Sales_USD AS SalesUSD, 
                           'Product2'         AS Product 
                    UNION ALL 
                    SELECT Product3_Sales_USD AS SalesUSD,
                           'Product3'         AS Product) UP