The Unpivot SQL is one of the most useful Operators to convert the Column names into Row values. Or say, Rotating Pivot table to regular table. Let us see how to convert Column names into Row values using Unpivot in SQL Server with example.
The below screenshot shows the data present in our [SQL Unpivot Source] table. Using this Pivot data, we will convert Column names (2011, 2012, 2013 and 2013) into Row values
UNPIVOT in SQL Example
In this Unpivot example, we will convert the Column names (2011, 12, 13, and 2014) into Row values using a built-in operator called Unpivot along with SQL IN Operator.
In this SQL example, the Product Name column values will remain the same. However, the Order Quantity rearranges a spermatozoon the Order Year.
-- Sql Server UNPIVOT Example SELECT [Product Name] ,[Order Year] ,[Order Quantity] FROM [SQL UnPivot Source] UNPIVOT ( [Order Quantity] FOR [Order Year] IN ([2011],[2012],[2013],[2014]) ) AS [UNPIVOT TABLE]
OUTPUT
UNPIVOT Alternatives
If the interviewer is willing to test your coding skills, then you can expect this question in the interviews. Following are some of the alternative approaches to achieve the Unpivot functionality in SQL Server
Using Cross Apply Values as Unpivot alternative
In this example, we are using the Cross apply to unpivot the above-specified pivot table.
-- Sql Server UNPIVOT Alternative Example SELECT Original.[Product Name] ,Unpivoted.[Order Year] ,Unpivoted.[Order Quantity] FROM [SQL UnPivot Source] AS Original CROSS APPLY ( VALUES ([2011], '2011') ,([2012], '2012') ,([2013], '2013') ,([2014], '2014') ) Unpivoted ([Order Year], [Order Quantity] )
OUTPUT
Unpivot alternative Case Statement along with Cross Join
In this example, We are going to use Case Statement along with Cross Join to achieve the SQL Unpivot functionality
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 [SQL UnPivot Source] ) AS Original CROSS JOIN ( SELECT '2011' UNION ALL SELECT '2012' UNION ALL SELECT '2013' UNION ALL SELECT '2014' ) Unpivoted ([Order Year])
OUTPUT
The simplified version of the above mentioned code is:
-- Sql UNPIVOT Alternative Example 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 [SQL UnPivot Source] ) AS Original CROSS JOIN ( VALUES ('2011'), ('2012'), ('2013'), ('2014') ) Unpivoted ([Order Year])
In this example, we used the Values keyword rather than using the old and traditional Union All
OUTPUT
Union All – Unpivot alternative
In this example, We are going to use Union All as an alternative to Unpivot. It might not be the ideal approach, but you get the point.
SELECT [Product Name], [Order Quantity] = [2011], [Order Year] = '2011' FROM [SQL UnPivot Source] UNION ALL SELECT [Product Name], [2012], '2012' FROM [SQL UnPivot Source] UNION ALL SELECT [Product Name], [2013], '2013' FROM [SQL UnPivot Source] UNION ALL SELECT [Product Name], [2014], '2014' FROM [SQL UnPivot Source] ORDER BY [Product Name]
OUTPUT