The SQL Unpivot 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 source screenshot shows the data present in our table. Using this Pivot data, we will convert or unpivot 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 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.
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 UNPIVOT Alternatives
If the interviewer is willing to test your coding skills, then you can expect the alternative approaches to achieve the Unpivot functionality in SQL Server
Using Cross Apply Values as an alternative
In this example, we are using the Cross apply to un pivot the above-specified pivot table.
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 the 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 [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:
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 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]
