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. They are especially helpful when we need to:

• Convert row values into columns for better summaries
• Convert columns back into rows for detailed analysis
• Create structured reports from raw data

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.

Using Pivot and Unpivot in SQL

• PIVOT - It is used when we want to summarize and organize data by turning rows into columns, making it easier to create reports and comparisons.
• UNPIVOT - It is used when we need to convert those columns back into rows, which helps in normalizing the data and doing further analysis.