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
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
