What is the purpose of the PIVOT and UNPIVOT operators in Transact-SQL?

The PIVOT and UNPIVOT operators are used to modify the structure of tables in Transact-SQL.

PIVOT transforms unique values from one column (Pivot Column) into new columns in the output table. It aggregates rows based on the value of another column (Grouping Column).

UNPIVOT performs the reverse action: it transforms columns into rows, effectively "normalizing" data from a wide table into a narrower and longer format.

Example of PIVOT:

Suppose there is a table Sales with columns Employee, Year, Amount.

Employee	Year	Amount
Alice	2020	100
Bob	2020	150
Alice	2021	120
Bob	2021	180

Using PIVOT to get annual sales by employee:

SELECT Employee, [2020], [2021]
FROM (
    SELECT Employee, Year, Amount
    FROM Sales
) AS SourceTable
PIVOT(
    SUM(Amount) -- Aggregating function
    FOR Year IN ([2020], [2021]) -- Column whose values become new columns
) AS PivotTable;

Result:

Employee	2020	2021
Alice	100	120
Bob	150	180

Example of UNPIVOT:

Suppose there is a table AnnualSales with columns Employee, [2020], [2021].

Employee	2020	2021
Alice	100	120
Bob	150	180

Using UNPIVOT to convert back to a format with columns Year and Amount:

SELECT Employee, Year, Amount
FROM AnnualSales
UNPIVOT(
    Amount FOR Year IN ([2020], [2021]) -- Column for value, column for original column names
) AS UnpivotTable;

Result:

Employee	Year	Amount
Alice	2020	100
Alice	2021	120
Bob	2020	150
Bob	2021	180

Both operators are used to simplify queries when working with "wide" or "narrow" tables, providing a more convenient format for data analysis or presentation from a relational structure.