SQL Query: How to Dynamically Convert Rows into Columns Using Dynamic Pivot ?

Converting rows into columns is one of the most useful techniques in SQL. While the PIVOT function is great, manually hardcoding column names can be a hassle—especially when your data evolves or includes a large set of potential column names. This tutorial will walk you through creating a dynamic pivot query in SQL Server.

By the end, you'll know how to generate column names based on row values dynamically, making your queries more flexible and robust.

What Is Dynamic Pivoting?

Pivoting transforms rows into columns, offering a different perspective on your data. A dynamic pivot query takes it a step further—columns are generated dynamically based on the data. No manual hardcoding needed.

For instance, imagine a table with these columns:

• ID: Unique identifier
• Name: Includes categories like Name, Gender, Salary
• Value: The actual values tied to each category

Dynamic pivoting ensures that if new categories like HireDate are added, they’ll automatically appear as new columns without modifying the query.

Preparing the Example Data

For this tutorial, we’ll use a demo table called Employee. It looks something like this:

ID	Name	Value
1	Name	John Doe
1	Gender	Male
1	Salary	50000
2	Name	Jane Smith
2	Gender	Female
2	Salary	60000

Our goal is to structure the data like this:

ID	Name	Gender	Salary
1	John Doe	Male	50000
2	Jane Smith	Female	60000

Writing the Static Pivot Query

First, let’s create a basic pivot query with hardcoded column names.

SELECT ID, [Name], [Gender], [Salary]  
FROM  
(  
    SELECT ID, Name, Value  
    FROM dbo.Employee  
) SourceTable  
PIVOT  
(  
    MAX(Value)  
    FOR Name IN ([Name], [Gender], [Salary])  
) PivotTable;  

This works well but doesn’t account for new categories like HireDate. That’s where dynamic pivoting comes in.

Generating Column Names Dynamically

The first step in dynamic pivoting is to extract the distinct categories (e.g., Name, Gender, Salary) from the Name column.

Here’s the query to get them:

SELECT DISTINCT Name  
FROM dbo.Employee;  

But we need these values as a comma-separated string. We’ll use the STRING_AGG function for that:

SELECT STRING_AGG(DISTINCT Name, ',') AS ColumnNames  
FROM dbo.Employee;  

This gives us Name,Gender,Salary. If your SQL Server version doesn’t support STRING_AGG, you can use STUFF and FOR XML PATH instead.

Storing Column Names in a Variable

Next, we’ll store the result in a variable so we can use it in the pivot query.

DECLARE @ColumnList NVARCHAR(MAX);  

SELECT @ColumnList = STRING_AGG(DISTINCT Name, ',')  
FROM dbo.Employee;  

Now, @ColumnList holds the value Name,Gender,Salary.

Building the Dynamic Query

We’ll dynamically construct the SQL pivot query as a string, substituting the column names stored in @ColumnList.

DECLARE @SQL NVARCHAR(MAX);  

SET @SQL = '  
SELECT ID, ' + @ColumnList + '  
FROM  
(  
    SELECT ID, Name, Value  
    FROM dbo.Employee  
) SourceTable  
PIVOT  
(  
    MAX(Value)  
    FOR Name IN (' + @ColumnList + ')  
) PivotTable;  
';  

To see how the query looks, add a PRINT @SQL statement.

Executing the Dynamic SQL

Finally, execute the query using the EXEC command:

EXEC(@SQL);  

This runs the dynamically generated SQL and yields your desired results.

Step-by-Step Walkthrough of the Code

1. Declare Variables: Declare variables for the column list and the dynamic SQL query.

   DECLARE @ColumnList NVARCHAR(MAX);  
   DECLARE @SQL NVARCHAR(MAX);  
   

2. Generate Column Names: Dynamically fetch categories from the data.

   SELECT @ColumnList = STRING_AGG(DISTINCT Name, ',')  
   FROM dbo.Employee;  
   

3. Build the Query: Construct the pivot SQL query as a string.

   SET @SQL = '  
   SELECT ID, ' + @ColumnList + '  
   FROM  
       (SELECT ID, Name, Value FROM dbo.Employee) SourceTable  
   PIVOT  
       (MAX(Value) FOR Name IN (' + @ColumnList + ')) PivotTable;  
   ';  
   

4. Execute the Query: Run the final query using EXEC.

   EXEC(@SQL);  
   

Advantages of Dynamic Pivoting

• Automatic Updates: No need to modify the query for new categories.
• Efficiency: Reduces manual effort in maintaining queries for evolving data.
• Scalability: Handles large datasets with numerous categories seamlessly.

Conclusion

Dynamic pivoting makes data transformation in SQL much more practical and flexible. It eliminates the need for manual hardcoding and adapts to changing datasets.

Whether you're building reports or reshaping data for analysis, dynamic pivot queries save time and effort. Next time your table changes, you’ll be ready.

If you found this guide helpful, subscribe to our updates for more SQL tips and tutorials!

Learn More

For any questions, reach out to learn@knowstar.org.

Consider expanding your expertise with these certifications:

• Google Data Analytics Professional Certificate: https://imp.i384100.net/OR37oQ
• Google Advanced Data Analytics Professional Certificate: https://imp.i384100.net/eK1WmQ

• Best SQL and Data Analytics Books

• T-SQL Fundamentals (By Itzik Ben-Gan) - https://amzn.to/4koKGdC
• Ace the Data Science Interview - https://amzn.to/3D2ne5n
•