SQL Server › Pivoting & Unpivoting Data

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

Answer

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