SQL Server Interview Question #174

What is the PIVOT operator?

Database Design, Normalization & Advanced SQL Senior Advanced

Detailed Explanation

PIVOT transforms values from rows into columns while applying an aggregate. It is useful for cross-tab reports such as displaying monthly totals as separate columns.

PIVOT is convenient when output columns are known in advance. If column values are dynamic, dynamic SQL is commonly required to generate the pivot column list safely.

Code Example

SELECT CategoryId, [2024], [2025], [2026]
FROM
(
    SELECT CategoryId,
           YEAR(CreatedAt) AS SalesYear,
           Price
    FROM dbo.Products
) AS SourceData
PIVOT
(
    SUM(Price)
    FOR SalesYear IN ([2024], [2025], [2026])
) AS P;