All 7 Pivoting & Unpivoting Data questions
More in Pivoting & Unpivoting Data
What does it mean to unpivot data, and how is it essentially the opposite operation of pivoting? How does the syntax of the PIVOT operator work, and what are its main components? 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

What does it mean to pivot data in SQL Server, and what kind of reporting need does it typically address?

Beginner

Answer

Pivoting transforms data from a row based format into a column based format, taking distinct values from one column and turning them into separate column headers, which is commonly used to build summary reports, such as showing monthly sales totals as separate columns instead of separate rows, making the data easier to read at a glance.
SELECT * FROM (
  SELECT ProductCategory, MonthName, Amount FROM Sales
) AS src
PIVOT (
  SUM(Amount) FOR MonthName IN ([January], [February], [March])
) AS pvt;
Real-world example A sales report shows each product category as a row with separate columns for January, February, and March sales totals, generated using the PIVOT operator instead of manually reshaping the data in a spreadsheet after the fact.

Common follow-ups: Do you need to know the exact column values ahead of time to use PIVOT?;What happens if a category has no sales at all for one of the pivoted months?

Aggregate Functions & GROUP BY;Data Types & Schema Design
All 7 Pivoting & Unpivoting Data questions