UNPIVOT in SQL Server to convert column to row

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:

[sourcecode language=”sql”]

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
[/sourcecode]

Here is the output of SELECT query. It shows customer name and quantity of order they have had in month of January and February.

1SELECTQuery

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.

[sourcecode language=”sql”]

 

SELECT *

FROM

(

SELECT Customer,Orders

FROM #UnPivotSampleTable

UNPIVOT

(

VALUE

FOR Orders in (January,February)

) UnPivo

) tab

GO

[/sourcecode]

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.

2Unpivot

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.

1 thought on “UNPIVOT in SQL Server to convert column to row”

Comments are closed.