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? 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
Back to results for “Normalization”
SQL Server › Pivoting & Unpivoting Data

What does it mean to unpivot data, and how is it essentially the opposite operation of pivoting?

Beginner

Answer

Unpivoting transforms data from a column based format back into a row based format, taking several separate columns and converting them into rows with a category label and a corresponding value, which is useful when you receive data in a wide, spread out format but need it in a more normalized, row based structure for further processing.
SELECT ProductId, MonthName, Amount
FROM MonthlySales
UNPIVOT (
  Amount FOR MonthName IN ([January], [February], [March])
) AS unpvt;
Real-world example A data migration process receives sales data with separate columns for each month, and uses UNPIVOT to convert it back into a clean, normalized row based format before loading it into the target system.

Common follow-ups: What data type restrictions apply to the columns being unpivoted?;Can UNPIVOT handle a source table with a very large number of columns to convert?

Data Types & Schema Design;Normalization
All 7 Pivoting & Unpivoting Data questions