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.