Open In App

Pivot and Unpivot in SQL

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Share
Report issue
Report

In SQL, Pivot and Unpivot are relational operators that are used to transform one table into another in order to achieve more simpler view of table. Conventionally we can say that Pivot operator converts the rows data of the table into the column data. The Unpivot operator does the opposite that is it transform the column based data into rows.

Syntax:

1. Pivot:

SELECT (ColumnNames) 
FROM (TableName) 
PIVOT
 ( 
   AggregateFunction(ColumnToBeAggregated)
   FOR PivotColumn IN (PivotColumnValues)
 ) AS (Alias) //Alias is a temporary name for a table

2. Unpivot:

SELECT (ColumnNames) 
FROM (TableName) 
UNPIVOT
 ( 
   AggregateFunction(ColumnToBeAggregated)
   FOR PivotColumn IN (PivotColumnValues)
 ) AS (Alias)

Example-1:
We have created a simple table named “geeksforgeeks” with values like Course name, course category and price and inserted the respective values.

Create Table geeksforgeeks 
( 
CourseName nvarchar(50), 
CourseCategory nvarchar(50),
Price int  
) 

Insert into geeksforgeeks  values('C', 'PROGRAMMING', 5000) 
Insert into geeksforgeeks  values('JAVA', 'PROGRAMMING', 6000) 
Insert into geeksforgeeks  values('PYTHON', 'PROGRAMMING', 8000) 
Insert into geeksforgeeks  values('PLACEMENT 100', 'INTERVIEWPREPARATION', 5000) 

SELECT * FROM geeksforgeeks 

The output we get is :

CourseName CourseCategory Price
C PROGRAMMING 5000
JAVA PROGRAMMING 6000
PYTHON PROGRAMMING 8000
PLACEMENT 100 INTERVIEWPREPARATION 5000

Now, applying PIVOT operator to this data:

SELECT CourseName, PROGRAMMING, INTERVIEWPREPARATION
FROM geeksforgeeks 
PIVOT 
( 
SUM(Price) FOR CourseCategory IN (PROGRAMMING, INTERVIEWPREPARATION ) 
) AS PivotTable 

After using Pivot operator we get the following result:

CourseName PROGRAMMING InterviewPreparation
C 5000 NULL
JAVA 6000 NULL
PLACEMENT 100 NULL 5000
PYTHON 8000 NULL

Example-2:
Now, we use the same table “geeksforgeeks” created in the above example and apply the Unpivot operator to our Pivoted table.

Applying UNPIVOT operator:

SELECT CourseName, CourseCategory, Price 
FROM 
(
SELECT CourseName, PROGRAMMING, INTERVIEWPREPARATION FROM geeksforgeeks 
PIVOT 
( 
SUM(Price) FOR CourseCategory IN (PROGRAMMING, INTERVIEWPREPARATION) 
) AS PivotTable
) P 
UNPIVOT 
( 
Price FOR CourseCategory IN (PROGRAMMING, INTERVIEWPREPARATION)
) 
AS UnpivotTable

After using Unpivot operator we get our original table back as we have successfully transformed the columns of the table back to rows:

CourseName CourseCategory Price
C PROGRAMMING 5000
JAVA PROGRAMMING 6000
PLACEMENT 100 INTERVIEWPREPARATION 5000
PYTHON PROGRAMMING 8000



Last Updated : 24 Jul, 2019
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads