UNPIVOT in SQL

The SQL Server Unpivot is one of the most useful Operators for converting Column names into Row values. Or, say, Rotating the Pivot table to a regular table. Let us see how to convert Column names into Row values using Unpivot with an example.

The below source screenshot shows the data present in our table. Using this Pivot data, we will convert or unpivot those Column names (2011, 2012, 2013, and 2013) into Row values.

UNPIVOT in SQL Example

In this example, we will convert the Column names (2011, 12, 13, and 2014) into Row values using this built-in operator. We have to use it along with the SQL IN operator. In this SQL example, the Product Name column values will remain the same. However, the Order Quantity rearranges a spermatozoon by the Order Year. For more fundamentals, refer to the SQL Language tutorial.

SELECT [Product Name]
      ,[Order Year]
      ,[Order Quantity]
FROM [SQLUnPivot Source]
UNPIVOT (
          [Order Quantity] FOR [Order Year] 
          IN ([2011],[2012],[2013],[2014]) 
	) AS [UNPIVOT TABLE]

SQL Server UNPIVOT Alternatives

If the interviewer is willing to test your coding skills, then you can expect alternative approaches to achieve the Unpivot functionality.

Using Cross Apply Values as an alternative

In this example, we are using the Cross Apply to unpivot the above-specified pivot table. Refer to the SQL PIVOT TABLE article.

SELECT Original.[Product Name] 
      ,Unpivoted.[Order Year]
      ,Unpivoted.[Order Quantity]     
FROM [SQLUnPivot Source] AS Original
CROSS APPLY 
 (
   VALUES 
         ([2011], '2011')
        ,([2012], '2012')
        ,([2013], '2013')
        ,([2014], '2014')
 ) Unpivoted ([Order Year], [Order Quantity] )

Unpivot alternative Case Statement along with Cross Join

Use Case Statement along with Cross Join to achieve this unpivot table columns functionality. Refer to the SQL CASE Statement and SQL CROSS JOIN articles.

SELECT 
   Original.[Product Name],
   Unpivoted.[Order Year],
   [Order Quantity]  = 
       CASE Unpivoted.[Order Year]
          WHEN '2011' THEN Original.[2011]
          WHEN '2012' THEN Original.[2012]
          WHEN '2013' THEN Original.[2013]
          WHEN '2014' THEN Original.[2014]
       END
FROM (
       SELECT [Product Name],[2011],[2012],[2013],[2014]
       FROM [SQLUnPivot Source]
     ) AS Original
CROSS JOIN 
  (
    SELECT '2011' UNION ALL
    SELECT '2012' UNION ALL
    SELECT '2013' UNION ALL
    SELECT '2014'
  ) Unpivoted ([Order Year])

The simplified version of the above code for pivoted columns is shown below. Here, the SQL SELECT Statement uses the SQL Nested Query to get the required data.

SELECT 
   Original.[Product Name],
   Unpivoted.[Order Year],
   [Order Quantity]  = 
       CASE Unpivoted.[Order Year]
          WHEN '2011' THEN Original.[2011]
          WHEN '2012' THEN Original.[2012]
          WHEN '2013' THEN Original.[2013]
          WHEN '2014' THEN Original.[2014]
       END
FROM (
       SELECT [Product Name],[2011],[2012],[2013],[2014]
	   FROM [SQLUnPivot Source]
     ) AS Original
CROSS JOIN 
  (
     VALUES ('2011'), ('2012'), ('2013'), ('2014')
  ) Unpivoted ([Order Year])

Using the Values keyword rather than using the old and traditional Union All.

Union All Unpivot alternative

In this example, we use SQL UNION ALL as an alternative. It might not be the ideal approach, but you get the point.

SELECT [Product Name], 
       [Order Quantity] = [2011], 
       [Order Year] = '2011' 
FROM [SQLUnPivot Source]
UNION ALL
SELECT [Product Name], [2012], '2012' 
FROM [SQLUnPivot Source]
UNION ALL
SELECT [Product Name], [2013], '2013' 
FROM [SQLUnPivot Source]
UNION ALL
SELECT [Product Name], [2014], '2014' 
FROM [SQLUnPivot Source]
ORDER BY [Product Name]