---
title: "How can you pivot rows into columns dynamically using T-SQL?"  
description: "How can you pivot rows into columns dynamically using T-SQL?"  
author: "Ashutosh Patel"  
published: 2025-04-30  
updated: 2025-05-03  
canonical: https://www.mindstick.com/forum/161554/how-can-you-pivot-rows-into-columns-dynamically-using-t-sql  
category: "SQL Server"  
tags: ["mssql server", "sql server", "sql", "SQL Database"]  
reading_time: 2 minutes  

---

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

How can you [pivot](https://www.mindstick.com/blog/163/pivot-tables-in-sqlserver-2005-2008) [rows into columns](https://answers.mindstick.com/blog/242/sql-pivot-table-transform-rows-into-columns) dynamically using T-SQL?

## Replies

### Reply by Khushi Singh

In [SQL Server](https://www.mindstick.com/articles/337216/explain-the-different-types-of-sql-server-authentication), if you need to turn rows into [columns](https://www.mindstick.com/articles/1511/pivot-with-dynamic-columns-in-sql-server) 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:

```plaintext
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.


---

Original Source: https://www.mindstick.com/forum/161554/how-can-you-pivot-rows-into-columns-dynamically-using-t-sql

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.