UNPIVOT Operator Using Stored Procedure In SQL Server

Introduction

UNPIVOT converts COLUMNS into ROWS.

Note

UNPIVOT operator converts COLUMNS into ROWS , where as PIVOT operator converts ROWS into COLUMNS.

Description

UNPIVOT operator is the reverse process of PIVOT operator.

 

Steps to follow

Create one table.

Create Table tblHomeNumbers
(
 Name nvarchar(50),
 Bbsr int,
 Bangalore int,
 London int
)

Insert some dummy records into this table.

Insert into tblHomeNumbers values ('Satyaprakash', 1, 2, 3)
Insert into tblHomeNumbers values ('Kulu', 9, 8, 7)

Execute the below script.

select * from tblHomeNumbers

 

 

Create a stored procedure to implement Unpivot Operator.

Create procedure Sp_Unpivot
As
Begin
SELECT Name, Places, Numbers
FROM tblHomeNumbers
UNPIVOT
(
       Numbers
       FOR Places IN (Bbsr, Bangalore ,London)
) AS UnpivotExample
END

Excute below script.

EXEC Sp_Unpivot

 

 

Here column names act as row values using UNPIVOT operator.

 
Summary

1. What is UNPIVOT operator.
2. Difference between UNPIVOT operator , PIVOT operator.
3. How to implement UNPIVOT operator using stored procedure in Sql Server.