UNPIVOT in SQL Server to convert column to row
Recently I got one new stored procedure to deploy in our live server. Before I deploy it to live, I have checked it and found heavy read and CPU in that SP. While investigating I found that CURSOR has been used in that SP and I can easily eliminate that CURSOR by using UNPIVOT in TSQL.
PIVOT and UNPIVOT both are wonderful enhancement in TSQL from SQL Server 2005. I really appreciate it, I have written many articles for PIVOT so far but unfortunately I haven’t written anything related to UNPIVOT in all these years so I just wanted to share this UNPIVOT feature with my blog reader so that SQL / .NET developer can use it whenever it is needed.
Pivot can easily converts your row data into column by aggregating values. Refer following links to learn more about PIVOT.
PIVOT –Fixed Column Cross Tab Query in Microsoft SQL Server 2005 (Click Here)
Dynamic PIVOT with month number to month name as header in SQL Server 2008 (Click Here)
PIVOT task in Data Flow Transformation in SSIS 2008 (Click Here)
Dynamic PIVOT with WHERE condition in SQL Server 2005 (Click Here)
Generic stored procedure for PIVOT in SQL Server (Click Here)
UNPIVOT is completely opposite to PIVOT. UNPIVOT converts your column to row. Let us understand this by an example:
Let us create one table with sample data:
IF OBJECT_ID('tempdb..#UnPivotSampleTable') IS NOT NULL DROP TABLE #UnPivotSampleTable CREATE TABLE #UnPivotSampleTable ( Customer VARCHAR(15) ,January INT ,February INT ) GO INSERT INTO #UnPivotSampleTable SELECT 'Ritesh',1,6 UNION ALL SELECT 'Rajan',NULL,8 UNION ALL SELECT 'Teerth',3,NULL GO SELECT * FROM #UnPivotSampleTable GO
Here is the output of SELECT query. It shows customer name and quantity of order they have had in month of January and February.
Now I just wanted to see which customer has given order in which month. I am not at all concern about the quantity; one of the easy ways to do this is UNPIVOT. Here is the TSQL for the same.
&nbsp; SELECT * FROM ( SELECT Customer,Orders FROM #UnPivotSampleTable UNPIVOT ( VALUE FOR Orders in (January,February) ) UnPivo ) tab GO
Here is the output of UNPIVOT which will show the name of month along with customer name. Ritesh has order in JAN as well as in FEB so there will be two rows for him and rest of the customer will have one row in result set.
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.