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.and 
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:and 
and 
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;
and 
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?

Ashutosh Kumar Verma

Can you answer this question?

1 Answers

Liked By