How can you pivot rows into columns dynamically using T-SQL?

Question

Khushi Singh · Answer

In SQL Server, if you need to turn rows into columns and you don’t know the column headers beforehand—like years, months, or products—dynamic pivoting is the way to go. Unlike regular pivoting where you’d have fixed column names, dynamic pivoting adjusts on the fly, which is perfect for flexible reporting. You can do this using dynamic SQL with the PIVOT operator.

Let’s take a look at an example with a Sales table that has three columns: Region, Year, and Amount. Here's a snapshot of the data:

Region	Year	Amount
East	2021	5000
West	2021	6000
East	2022	7000
West	2022	8000

We want to pivot this data to show Year values as columns, with total Amount as values, resulting in:

Region	2021	2022
East	5000	7000
West	6000	8000

To create this dynamically, you can use the following T-SQL:

DECLARE @cols NVARCHAR(MAX);
SELECT @cols = STRING_AGG(QUOTENAME(Year), ',')
FROM (SELECT DISTINCT Year FROM Sales) AS Y;
DECLARE @sql NVARCHAR(MAX);
SET @sql = '
SELECT Region, ' + @cols + '
FROM (
   SELECT Region, Year, Amount FROM Sales
) AS SourceTable
PIVOT (
   SUM(Amount) FOR Year IN (' + @cols + ')
) AS PivotTable;';
EXEC sp_executesql @sql;

In this code, we first get the unique years and build a string for the pivot columns. The QUOTENAME function makes sure the column names are correctly formatted, while STRING_AGG combines them for the pivot clause. This way, the script adapts well to any changes in the data, which is great for making dynamic dashboards or reports.

forum

How can you pivot rows into columns dynamically using T-SQL?

Can you answer this question?

1 Answers