Last Updated : 12 Jul, 2025
In SQL, PIVOT and UNPIVOT are powerful operations used to transform data and make it more readable, efficient, and manageable. These operations allow us to manipulate tables by switching between rows and columns, which can be crucial for summarizing data, reporting, and data analysis. Understanding how to use PIVOT and UNPIVOT operators effectively can significantly enhance our data manipulation capabilities.
In this article, we’ll explore what PIVOT and UNPIVOT are, their syntax, key differences, and provide practical examples with explanations to help us master these SQL operations.
What is Pivot in SQL?The PIVOT operation in SQL allows us to rotate data from rows to columns. This can be particularly useful when we need to summarize, aggregate, or reorganize data for reporting purposes. When we apply PIVOT, each unique value in a column is turned into its own column, and the data is aggregated based on a specified function, such as SUM, AVG, COUNT, etc.
Syntax:
Example of Pivot Operation:SELECT (ColumnNames)
FROM (TableName)
PIVOT
(
AggregateFunction(ColumnToBeAggregated)
FOR PivotColumn IN (PivotColumnValues)
) AS (Alias) //Alias is a temporary name for a table
Let’s say we have a sample table geeksforgeeks that contains data about various courses, their categories, and their prices. We can use the PIVOT operator to summarize the total price for each course category.
CourseName CourseCategory Price C PROGRAMMING 5000 JAVA PROGRAMMING 6000 PYTHON PROGRAMMING 8000 PLACEMENT 100 INTERVIEWPREPARATION 5000Query:
SELECT CourseName, PROGRAMMING, INTERVIEWPREPARATION
FROM geeksforgeeks
PIVOT
(
SUM(Price)
FOR CourseCategory IN (PROGRAMMING, INTERVIEWPREPARATION )
) AS PivotTable
Output
Output of Pivot operationExplanation:
The PIVOT operation reorganizes the data, transforming the CourseCategory values into columns, while the SUM(Price) function aggregates the prices based on these categories. Notice that the original rows are now represented as columns.
What is Unpivot in SQL?The UNPIVOT operation in SQL is the reverse of PIVOT. It allows us to convert columns back into rows, which can be useful when we need to normalize or restructure data after performing a pivot operation. UNPIVOT helps in data analysis where row-based operations are required for analysis.
Syntax:
Example of Unpivot Operation:SELECT (ColumnNames)
FROM (TableName)
UNPIVOT
(AggregateFunction(ColumnToBeAggregated)
FOR PivotColumn IN (PivotColumnValues)
) AS (Alias)
Now, we use the same table "geeksforgeeks" created in the above example and apply the UNPIVOT operation to reverse the PIVOT operation above and retrieve the original data.
Query:
SELECT CourseName, CourseCategory, Price
FROM
(
SELECT CourseName, PROGRAMMING, INTERVIEWPREPARATION
FROM geeksforgeeks
PIVOT
(
SUM(Price)
FOR CourseCategory IN (PROGRAMMING, INTERVIEWPREPARATION)
) AS PivotTable
) AS P
UNPIVOT
(
Price FOR CourseCategory IN (PROGRAMMING, INTERVIEWPREPARATION)
) AS UnpivotTable;
Output
Output of Unpivot operationExplanation:
The UNPIVOT operation transforms the column-based data (PROGRAMMING and INTERVIEWPREPARATION) back into rows, allowing us to return to the original structure. It essentially reverts the result set from the PIVOT operation.
When to Use Pivot and Unpivot in SQLUnderstanding the difference between PIVOT and UNPIVOT is essential for advanced SQL querying. These operations allow you to manipulate our data by rotating it, making it easier to analyze and report on. Whether we're transforming data for better presentation (using PIVOT) or reverting it back to a more normalized form (using UNPIVOT), mastering these SQL operations will significantly enhance our ability to work with relational databases.
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4