SQL Server › Pivoting & Unpivoting Data

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

Answer

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