Pivot and Unpivot in SQL

Pivot and Unpivot are two important operations in SQL that are used to transform data from rows to columns and vice versa.

Pivot

Pivot is used to transform rows into columns. It is used to aggregate data and display it in a more readable format. The PIVOT operator rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output.

Syntax

SELECT <non-pivoted column>,
    [first pivoted column] AS <column name>,
    [second pivoted column] AS <column name>,
    ...
    [last pivoted column] AS <column name>
FROM
    (<SELECT query that produces the data>)
    AS <alias for the source query>
PIVOT
(
    <aggregation function>(<column to be aggregated>)
FOR
[<column that contains the values that will become column headers>]
    IN ( [first pivoted column], [second pivoted column],
    ... [last pivoted column])
) AS <alias for the pivot table>

Example

Consider the following table Sales:

To pivot the table on the Month column, we can use the following query:

SELECT Product, Jan, Feb
FROM
(
    SELECT Product, Month, Sales
    FROM Sales
) AS SourceTable
PIVOT
(
    SUM(Sales)
    FOR Month IN (Jan, Feb)
) AS PivotTable;

The output of the query will be:

Unpivot

Unpivot is used to transform columns into rows. It is used to normalize data and make it easier to analyze. The UNPIVOT operator performs the opposite operation of PIVOT, by rotating columns into rows.

Syntax

SELECT <non-unpivoted column>,
    <unpivoted column name>,
    <unpivoted column value>
FROM
    (<SELECT query that produces the data>)
    AS <alias for the source query>
UNPIVOT
(
    <unpivoted column value>
    FOR <unpivoted column name> IN
    (<column1>, <column2>,
    ... <columnn>)
) AS <alias for the unpivoted table>

Example

Consider the following table Sales:

To unpivot the table, we can use the following query:

SELECT Product, Month, Sales
FROM
(
    SELECT Product, Jan, Feb
    FROM Sales
) AS SourceTable
UNPIVOT
(
    Sales FOR Month IN (Jan, Feb)
) AS UnpivotTable;

The output of the query will be: