Post
Ask Question

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.
  1. Create Table tblHomeNumbers  
  2. (  
  3.  Name nvarchar(50),  
  4.  Bbsr int,  
  5.  Bangalore int,  
  6.  London int  
  7. ) 
Insert some dummy records into this table.
  1. Insert into tblHomeNumbers values ('Satyaprakash', 1, 2, 3)  
  2. Insert into tblHomeNumbers values ('Kulu', 9, 8, 7) 
Execute the below script.
  1. select * from tblHomeNumbers 
 
 
Create a stored procedure to implement Unpivot Operator.
  1. Create procedure Sp_Unpivot  
  2. As   
  3. Begin  
  4. SELECT Name, Places, Numbers  
  5. FROM tblHomeNumbers  
  6. UNPIVOT  
  7. (  
  8.        Numbers  
  9.        FOR Places IN (Bbsr, Bangalore ,London)  
  10. ) AS UnpivotExample  
  11. END   
Excute below script.
  1. 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.