All 7 Pivoting & Unpivoting Data questions
More in Pivoting & Unpivoting Data
What does it mean to pivot data in SQL Server, and what kind of reporting need does it typically address? What does it mean to unpivot data, and how is it essentially the opposite operation of pivoting? How would you handle a pivoting scenario where the set of columns needed is not known ahead of time and could change over time? What are some practical, real world use cases where PIVOT and UNPIVOT are commonly applied in business reporting? How would you build a cross tabulation report using PIVOT that shows multiple aggregate calculations, such as both total and average, for each category? What performance considerations should you keep in mind when using PIVOT or UNPIVOT on very large datasets?
Advertisement
SQL Server › Pivoting & Unpivoting Data

How does the syntax of the PIVOT operator work, and what are its main components?

Intermediate

Answer

The PIVOT operator requires an aggregate function applied to the value you want to summarize, the FOR keyword followed by the column whose distinct values will become new column headers, and an explicit IN clause listing exactly which of those distinct values should actually appear as columns in the final result.
SELECT Category, [2024], [2025], [2026]
FROM (
  SELECT Category, SalesYear, Revenue FROM YearlySales
) AS src
PIVOT (
  SUM(Revenue) FOR SalesYear IN ([2024], [2025], [2026])
) AS pvt;
Real-world example A yearly revenue report pivots data so that each year appears as its own column alongside a row for each product category, making year over year comparisons much easier to read than scrolling through many separate rows.

Common follow-ups: Why must the values in the IN clause be known and listed explicitly ahead of time?;What happens if a new year appears in the data that was not included in the IN clause?

Aggregate Functions & GROUP BY;Dynamic SQL
All 7 Pivoting & Unpivoting Data questions