Pivot and Unpivot in SQL

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:

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

Example of Pivot Operation:

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.

Query:

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

Output

Explanation:

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:

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

Example of Unpivot Operation:

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

Explanation:

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 SQL

• PIVOT: When you need to summarize or aggregate data and display it in a columnar format, typically for reporting purposes.
  
• UNPIVOT: When you need to convert column data back to rows, often after performing a PIVOT operation, for normalization or further analysis.

Conclusion

Understanding 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.