Pivoting & Unpivoting Data
7 questions found
What does it mean to pivot data in SQL Server, and what kind of reporting need does it typically address?
Beginner
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
What does it mean to unpivot data, and how is it essentially the opposite operation of pivoting?
Beginner
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
How does the syntax of the PIVOT operator work, and what are its main components?
Intermediate
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
How would you handle a pivoting scenario where the set of columns needed is not known ahead of time and could change over time?
Intermediate
Since the standard PIVOT operator requires you to list the exact column values explicitly, you combine it with dynamic SQL, first querying to find the current distinct values that should become columns, and then building and executing a dynamic PIVOT statement that includes exactly those values, allowing the report to automatically adapt as the underlying data changes.
DECLARE @Columns NVARCHAR(MAX);
SELECT @Columns = STRING_AGG(QUOTENAME(SalesYear), ',') FROM (SELECT DISTINCT SalesYear FROM YearlySales) AS Years;
DECLARE @SQL NVARCHAR(MAX) = N'SELECT * FROM (SELECT Category, SalesYear, Revenue FROM YearlySales) AS src PIVOT (SUM(Revenue) FOR SalesYear IN (' + @Columns + N')) AS pvt';
EXEC sp_executesql @SQL;
Real-world example
A reporting system automatically adjusts its yearly revenue report to include a new column whenever a new year of sales data appears, using dynamic SQL to build the PIVOT statement based on whatever years currently exist in the data.
Common follow-ups: What does the STRING_AGG function do in this context?;How do you safely build this kind of dynamic pivot without introducing a SQL injection risk?
Dynamic SQL;Aggregate Functions & GROUP BY
How would you build a cross tabulation report using PIVOT that shows multiple aggregate calculations, such as both total and average, for each category?
Advanced
Since a single PIVOT operator only supports one aggregate function at a time, you would either run two separate pivoted queries for each aggregate and join their results together, or use conditional aggregation with CASE expressions inside a regular GROUP BY query, which offers more flexibility for combining multiple different aggregate calculations in a single result.
SELECT Category,
SUM(CASE WHEN SalesYear = 2026 THEN Revenue END) AS Total2026,
AVG(CASE WHEN SalesYear = 2026 THEN Revenue END) AS Avg2026
FROM YearlySales
GROUP BY Category;
Real-world example
A financial analyst builds a detailed cross tabulation report showing both the total and average revenue per category for a specific year, using conditional aggregation with CASE expressions since a single PIVOT could not handle two different aggregate calculations at once.
Common follow-ups: When is conditional aggregation with CASE a better choice than the PIVOT operator?;How do you handle NULL values that might result from this kind of conditional aggregation?
Aggregate Functions & GROUP BY;Data Types & Schema Design
What performance considerations should you keep in mind when using PIVOT or UNPIVOT on very large datasets?
Advanced
Both operations still require SQL Server to process and aggregate the underlying data, so performance depends heavily on whether appropriate indexes exist on the columns used for grouping and filtering, and pivoting a very wide range of distinct values into many separate columns can become inefficient, sometimes making a well designed reporting table or a business intelligence tool a better long term solution for very large scale pivoting needs.
-- Ensure an index supports the underlying aggregation
CREATE INDEX IX_YearlySales_Category_Year
ON YearlySales(Category, SalesYear) INCLUDE (Revenue);
Real-world example
A team notices a PIVOT based report slowing down significantly as their sales data grows, and after adding a covering index to support the underlying aggregation, the report's performance improves substantially.
Common follow-ups: At what point does it make sense to move pivoting logic into a dedicated reporting or business intelligence tool instead of doing it in SQL?;How do you measure whether PIVOT is actually the performance bottleneck in a slow report?
Query Optimization & Plans;Indexes
What are some practical, real world use cases where PIVOT and UNPIVOT are commonly applied in business reporting?
Intermediate
PIVOT is commonly used to turn monthly, quarterly, or yearly data into side by side columns for trend comparison reports, survey response summaries showing counts per answer choice as columns, and inventory reports showing stock levels across different warehouse locations, while UNPIVOT is often used when importing spreadsheet style data that needs to be normalized back into a proper row based format for storage.
-- Survey results pivoted to show response counts per answer choice
SELECT QuestionId, [Yes], [No], [Maybe]
FROM SurveyResponses
PIVOT (COUNT(ResponseId) FOR Answer IN ([Yes], [No], [Maybe])) AS pvt;
Real-world example
A customer feedback system pivots survey response data to show the count of yes, no, and maybe answers as separate columns for each question, making it easy for stakeholders to quickly compare responses across many questions at once.
Common follow-ups: What other business reporting scenarios commonly benefit from pivoting?;How do you decide whether to do this transformation in SQL versus in a reporting tool like Power BI?
Aggregate Functions & GROUP BY;Data Types & Schema Design