Skip to content
Related Articles

Related Articles

Improve Article

Pivot and Unpivot in SQL

  • Difficulty Level : Medium
  • Last Updated : 24 Jul, 2019

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 :

CourseNameCourseCategoryPrice
CPROGRAMMING5000
JAVAPROGRAMMING6000
PYTHONPROGRAMMING8000
PLACEMENT 100INTERVIEWPREPARATION5000

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:

CourseNamePROGRAMMINGInterviewPreparation
C5000NULL
JAVA6000NULL
PLACEMENT 100NULL5000
PYTHON8000NULL

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:

CourseNameCourseCategoryPrice
CPROGRAMMING5000
JAVAPROGRAMMING6000PLACEMENT 100INTERVIEWPREPARATION5000
PYTHONPROGRAMMING8000


Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course  by GeeksforGeeks.

My Personal Notes arrow_drop_up
Recommended Articles
Page :