UNPIVOT in SQL

The Unpivot SQL is one of the most useful Operator to convert the Column names into Row values. Or simply say, Rotating Pivot table to normal table. In this article we will show you, How to convert Column names into Row values using Unpivot in SQL Server with example.

Below screenshot will show you the data present in our [SQL Unpivot Source] table. Using this 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, 2012, 2013 and 2013) into Row values using built-in operator called Unpivot along with SQL IN Operator.

Here, the Product Name column values will remain same. However, the Order Quantity will be 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 in SQL 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 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

SQL Unpivot alternative – Use Case Statement along with Cross Join

In this example, We are going to 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 [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

Unpivot alternative in SQL Union All

In this example, We are going to use Union All as alternative to Unpiot. This 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

Thank You for Visiting Our Blog