Pivot and Unpivot in SQL

Pivot and Unpivot in SQL are used to transform data by switching rows and columns. They help make data more readable, organized and useful for reporting and analysis.

Pivot in SQL

Pivot in SQL is used to change rows into columns. It helps show data in a table format that is easier to read and analyze. We usually use functions like SUM, COUNT or AVG to summarize the data. Each different value becomes a new column in the result.

Syntax:

SELECT column_names
FROM table_name
PIVOT (
    aggregate_function(column_to_aggregate)
    FOR pivot_column IN (pivot_values)
) AS alias;   -- alias is a temporary name for the result table

Example

We create a table named geeksforgeeks to store course details and then use PIVOT to get 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:

• PIVOT converts CourseCategory rows into columns.
• SUM(Price) calculates the total price for each category.

Unpivot in SQL

UNPIVOT is the reverse of PIVOT in SQL. It converts column-based data back into rows, which helps in reorganizing and normalizing the data so it can be easily analyzed in row format.

Syntax:

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

Example of Unpivot Operation

We use the same geeksforgeeks table and apply UNPIVOT to reverse the PIVOT and get back the original data format.

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:

• The column values are converted back into rows for each course.
• The result looks like the original table structure with category and price.