How to UNPIVOT Data Using CROSS APPLY In SQL Server

Posted November 28, 2020 by Vishwanath Dalvi in Database, SQL Server

APPLY operators (CROSS APPLY & OUTER APPLY) brings a lot to programming in SQL Server. Different use cases can be implemented using APPLY operators. One of the most used UNPIVOT data operators can also be implemented with the CROSS APPLY operator. This tech-recipe post demonstrates how to UNPIVOT data using CROSS APPLY in SQL Server.

In brief Unpivot data with CROSS APPLY works because the actual UNPIVOT operator uses correlated LEFT JOIN internally. Therefore UNPIVOT works like APPLY operators under the hood. Knowing different methods of Unpivot data can help us to find the best-optimized method.

In the previous tech-recipe post we have seen How to Reuse Calculated Column with CROSS APPLY In SQL Server.

Few other use cases of APPLY operators are worth learning as follows.

1. Unpivot the table data
2. Reuse column alias & calculated columns
3. Retrieving Top N records per group
4. Calling Table Valued Function for each row of the outer table

Unpivot Using CROSS APPLY

1.

Example 1.1 – UNPIVOT Data Using CROSS APPLY

Populating sample data with Student table to Unpivot with CROSS APPLY & native UNPIVOT operator.

Use tempdb;

DROP TABLE IF EXISTS dbo.Student;

CREATE TABLE Student
(
   [Name]     VARCHAR(50)
  ,[Maths]    INT
  ,[Science]  INT
  ,[English]  INT
);
    
INSERT INTO Student
    ([Name], [Maths], [Science], [English])
VALUES
    ('Atul',     90, 80, 70),
    ('Vishal',   80, 90, 60),
    ('Shailesh', 95, 85, 99);

SELECT * FROM Student; 


Name       Maths       Science     English
---------- ----------- ----------- -----------
Atul       90          80          70
Vishal     80          90          60
Shailesh   95          85          99


Following queries demonstrates how to Unpivot using CROSS APPLY and native UNPIVOT operator producing the same result set.

--Usual UNPIVOT Operator to Unpivot columns into rows.

SELECT Name, Subject, Marks
FROM   Student
UNPIVOT
(
  Marks
  FOR Subject in (Maths, Science, English)
) Upt
;

--Using CROSS APPLY to Unpivot columns into rows.

SELECT Name
      ,Upt.Subject
      ,Upt.Marks
FROM   student
       CROSS APPLY (VALUES(Maths,'Maths'),
                          (Science,'Science'),
                          (English,'English')) Upt(Marks,Subject); 


Both queries listed above produces same result data set as following.

Name               Subject Marks
------------------ ------- -----------
Atul               Maths   90
Atul               Science 80
Atul               English 70
Vishal             Maths   80
Vishal             Science 90
Vishal             English 60
Shailesh           Maths   95
Shailesh           Science 85
Shailesh           English 99

(9 rows affected)


2.

Example 1.2 – UNPIVOT Data Using CROSS APPLY

Populating sample data with Pvt table to Unpivot with CROSS APPLY & native UNPIVOT operator. This example is taken from Microsoft official documentation examples.

USE tempdb;

-- Create the table and insert values as portrayed in the previous example. 

DROP TABLE IF EXISTS pvt;

CREATE TABLE pvt (VendorID INT, Emp1 INT, Emp2 INT,  
    Emp3 INT, Emp4 INT, Emp5 INT);  
GO  
INSERT INTO pvt VALUES (1,4,3,5,4,4);  
INSERT INTO pvt VALUES (2,4,1,5,5,5);  
INSERT INTO pvt VALUES (3,4,3,5,4,4);  
INSERT INTO pvt VALUES (4,4,2,5,5,4);  
INSERT INTO pvt VALUES (5,5,1,5,5,5);  
GO  

SELECT * FROM pvt;


VendorID    Emp1        Emp2        Emp3        Emp4        Emp5
----------- ----------- ----------- ----------- ----------- -----------
1           4           3           5           4           4
2           4           1           5           5           5
3           4           3           5           4           4
4           4           2           5           5           4
5           5           1           5           5           5


Following queries demonstrates how to Unpivot using CROSS APPLY and native UNPIVOT operator producing the same result set.

-- Unpivot the table using native UNPIVOT operator.  
SELECT VendorID, Employee, Orders  
FROM   
   (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5  
   FROM pvt) p  
UNPIVOT  
   (Orders FOR Employee IN   
      (Emp1, Emp2, Emp3, Emp4, Emp5)  
)AS unpvt;  
GO  

--Using CROSS APPLY to Unpivot columns into rows.
SELECT VendorID
	  ,Upt.Employee
      ,Upt.Orders
FROM   pvt
       CROSS APPLY (VALUES(Emp1,'Emp1'),
                          (Emp2,'Emp2'),
                          (Emp3,'Emp3'),
			  (Emp4,'Emp4'),
			  (Emp5,'Emp5')
			) Upt(Orders,Employee); 


Both queries listed above produces same result data set as following.

VendorID    Employee Orders
----------- -------- -----------
1           Emp1     4
1           Emp2     3
1           Emp3     5
1           Emp4     4
1           Emp5     4
2           Emp1     4
2           Emp2     1
2           Emp3     5
2           Emp4     5
2           Emp5     5
...


Summary

In a nutshell, we have learned the specific use of CROSS APPLY. Unpivoting data using CROSS APPLY operators. In the next series of articles, we will learn more use cases of APPLY operators. If you like this post you may reach through Tech-Recipes database archive to learn more stuff.

Read more and browse through more posts related to SQL Server on Tech-Recipes.

1. How to Reuse Calculated Column with CROSS APPLY In SQL Server
2. How To Import CSV File Using Bulk Insert In SQL Server
3. SSIS- How To Export & Import ISPAC File SSISDB – Visual Studio
4. How To Create Database Diagram In SQL Server SSMS
5. How To Index Computed Column In SQL Server
6. How To Use Computed Column In SQL Server
7. Execute SQL Files Using SQLCMD

 

About Vishwanath Dalvi

Vishwanath Dalvi is a gifted engineer and tech enthusiast. He enjoys music, magic, movies, and gaming. When not hacking around or supporting the open source community, he is trying to overcome his phobia of dogs.
View more articles by Vishwanath Dalvi

The Conversation

Follow the reactions below and share your own thoughts.

Leave a Reply