How Do You Create a Dynamic PIVOT Query in an SQL Server?

In SQL Server, a dynamic PIVOT query allows you to convert row values into dynamic column headers, ideal for scenarios where the column name changes over time. Unlike static PIVOT, dynamic PIVOTs eliminate the need to predefine column names, making your SQL reports more flexible and automated. In this article, we’ll learn about multiple methods to implement dynamic PIVOT queries in SQL Server using STRING_AGG(), STUFF() with FOR XML PATH, sp_executesql, EXEC(), Common Table Expressions (CTEs), and Temporary Tables, along with practical examples, performance tips, and best practices.

Table of Contents:

• What is a Dynamic PIVOT Query in SQL Server?
• Why Use Dynamic PIVOT Queries in SQL Server?
• Benefits of Using Dynamic PIVOT in SQL Server
• How to Create a Dynamic PIVOT Query in SQL Server?
  • Method 1: Using STUFF and FOR XML PATH in SQL Server
  • Method 2: Using STRING_AGG in SQL Server
  • Method 3: Using Temp Table or Table Variable  in SQL Server
  • Method 4: Using CTE  in SQL Server
• Alternative Techniques to Build a Dynamic PIVOT Query
  • Method 1: Using EXEC() for Dynamic PIVOT
  • Method 2: Using sp_executesql to Run Dynamic PIVOT in SQL Server
• Dynamic PIVOT for Pivoting Multiple Columns in SQL Server
• Debugging Tips for Dynamic PIVOT Queries
• Performance Comparison of Dynamic PIVOT Methods
• Real-world Examples of Dynamic PIVOT in SQL Server
• Best Practices for Writing Dynamic PIVOT Queries
• Conclusion

What is a Dynamic PIVOT Query in SQL Server?

It is a query in SQL Server that dynamically creates column names at runtime based on the data in the table, instead of hardcoding them. To construct the PIVOT query, ‘EXEC sp_executesql’ can be used. When the column values are not fixed, the Dynamic PIVOT SQL Server Query is useful for reporting and data transformation. 

Why Use Dynamic PIVOT Queries in SQL Server?

A dynamic PIVOT SQL Server Query has to be used when the column values are not fixed and change over time. Without hardcoding the column names, the rows can be dynamically transformed into columns, making the query more flexible for different datasets. Without manual modifications, by using dynamic SQL, the query can be automatically adjusted to the new data.

Benefits of Using Dynamic PIVOT in SQL Server

• Efficient Reporting: Useful in creating dashboards and reports where column names change over time.
• High Scalability: Without requiring frequent updates, it handles large datasets efficiently.
• Reduces Maintenance Effort: Since the new data is updated automatically, it reduces the manual efforts
• High Flexibility: Without manually modifying the SQL query, it adapts to the changing data automatically.

How to Create a Dynamic PIVOT Query in SQL Server?

Depending on the versions and use cases, a Dynamic PIVOT query in SQL Server can be created using a variety of techniques, such as STUFF and STRING_AGG. 

Before getting into the methods, let us create an Employee Attendance table and insert some values into it, which will be used to perform the following methods:

Dynamic pivot example using STRING_AGG:

CREATE TABLE Em_Atd (
    Em_ID INT,
    Atd_Mon VARCHAR(20),
    Atd_dy INT
);
INSERT INTO Em_Atd (Em_ID, Atd_Mon, Atd_dy) VALUES
(101, 'January', 20),
(101, 'February', 18),
(102, 'January', 22),
(102, 'February', 21),
(103, 'January', 19),
(103, 'March', 20);
Select * from Em_Atd;

Output:

This is how the table looks once it is created and inserted with values.

Method 1: Using STUFF and FOR XML PATH in SQL Server

Within a standard PIVOT query, we cannot dynamically generate column names; we use STUFF and FOR XML PATH functions to dynamically construct the column lists.

Syntax:

SET QUOTED_IDENTIFIER ON;
DECLARE @Co NVARCHAR(MAX);
DECLARE @SQL NVARCHAR(MAX);
SELECT @Co = STUFF((
    SELECT DISTINCT ',' + QUOTENAME(co_nam) 
    FROM Tab_nam
EXEC sp_executesql @SQL;

Example:

SET QUOTED_IDENTIFIER ON;
DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);
SELECT @cols = STUFF((
    SELECT DISTINCT ',' + QUOTENAME(Atd_Mon)
    FROM Em_Atd
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '');
SET @sql = '
SELECT * FROM (
    SELECT Em_ID, Atd_Mon, Atd_dy FROM Em_Atd
) AS SourceTable
PIVOT (
    MAX(Atd_dy) FOR Atd_Mon IN (' + @cols + ')
) AS PivotResult;
';
EXEC sp_executesql @sql;

Output: 

Explanation: Here, it creates a list of unique months separated by commas using concatenation with FOR XML PATH. STUFF will remove the leading comma from the list so that it will be properly formatted for use in the dynamic column list of the PIVOT statement.

Method 2: Using STRING_AGG in SQL Server

This STRING_AGG function allows dynamic concatenation of column values into comma-separated lists.

Syntax:

SET QUOTED_IDENTIFIER ON;
SELECT @Columns = STRING_AGG(QUOTENAME(CoPt), ', ')
FROM (SELECT DISTINCT CoPt FROM Tab_nam) AS List;

EXEC sp_executesql @SQL;

Example:

DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);
SELECT @cols = STRING_AGG(QUOTENAME(Atd_Mon), ',')
FROM (SELECT DISTINCT Atd_Mon FROM Em_Atd) AS DistinctMonths;

SET @sql = '
SELECT * FROM (
    SELECT Em_ID, Atd_Mon, Atd_dy FROM Em_Atd
) AS SourceTable
PIVOT (
    MAX(Atd_dy) FOR Atd_Mon IN (' + @cols + ')
) AS PivotTable;';
EXEC sp_executesql @sql;

Output:

Explanation: Here, it constructs a unique, comma-separated list of months using QUOTENAME and STRING_AGG; These month names are then passed into the dynamic PIVOT to generate column headings such as [January], [February], etc..

Method 3: Using Temp Table or Table Variable  in SQL Server

Before performing the PIVOT operation, Temp Table (#TempTable) or Table Variable (@TableVariable) can be used to store the intermediate results.

Syntax:

SET QUO_IDNT ON;
SET @SQL = '
SELECT * FROM (
    SELECT  CoA FROM #TempTable
) AS SrTab
PIVOT (
    AggregateFunction(C3) FOR C2 IN (' + @Cos + ')
) AS PivotTable;
';
EXEC sp_executesql @SQL;
DROP TABLE #TempTable;

Example:

DECLARE @cols NVARCHAR(MAX) = '', @sql NVARCHAR(MAX);
CREATE TABLE #Months (MonthName VARCHAR(50));

INSERT INTO #Months
SELECT DISTINCT Atd_Mon FROM Em_Atd;
SELECT @cols = STRING_AGG(QUOTENAME(MonthName), ',') FROM #Months;
SET @sql = '
SELECT * FROM (
    SELECT Em_ID, Atd_Mon, Atd_dy FROM Em_Atd
) AS SourceTable
PIVOT (
    MAX(Atd_dy) FOR Atd_Mon IN (' + @cols + ')
) AS PivotTable;';
EXEC sp_executesql @sql;
DROP TABLE #Months;

Output:

Explanation:  Here, it stores the unique month values in a temporary table and then uses STRING_AGG to create the pivot column list. That dynamic list translates the Atd_dy values into monthly columns for every employee.

Method 4: Using CTE  in SQL Server

The CTE is a temporary result set that is defined within the scope of the WITH statement.

Syntax:

SET QUOTED_IDENTIFIER ON;
DECLARE @Columns NVARCHAR(MAX);
DECLARE @SQL NVARCHAR(MAX);
SELECT @Columns = STRING_AGG(QUOTENAME(Column2), ', ')
FROM (SELECT DISTINCT Column2 FROM SourceTable) AS ColumnList;
SET @SQL = '
WITH CTE_Name AS (
    -- Define the CTE
    SELECT Column1, Column2, Column3
    FROM SourceTable
    WHERE SomeCondition 
)
SELECT * FROM (
    -- Use the CTE as the source for the PIVOT
    SELECT Column1, Column2, Column3 FROM CTE_Name
) AS SourceTable
PIVOT (
    AggregateFunction(Column3) FOR Column2 IN (' + @Columns + ')
) AS PivotTable;
';
EXEC sp_executesql @SQL;

Example:

DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);
WITH MonthList AS (
    SELECT DISTINCT Atd_Mon FROM Em_Atd
)
SELECT @cols = STRING_AGG(QUOTENAME(Atd_Mon), ',') FROM MonthList;
SET @sql = '
WITH BaseData AS (
    SELECT Em_ID, Atd_Mon, Atd_dy FROM Em_Atd
)
SELECT * FROM BaseData
PIVOT (
    MAX(Atd_dy) FOR Atd_Mon IN (' + @cols + ')
) AS PivotTable;';
EXEC sp_executesql @sql;

Output:

Explanation: Here, the CTE selects unique month names to create an aggregate column list for pivoting. That list then generates the columns dynamically for transforming rows into a pivot table representation.

Alternative Techniques to Build a Dynamic PIVOT Query

There are a few alternative approaches for creating a Dynamic PIVOT query, like using EXEC() and using sp_executesql.

Method 1: Using EXEC() for Dynamic PIVOT

The EXEC() in SQL Server is another way through which we can dynamically generate SQL Statements. In this approach, a PIVOT query can be constructed as a string and then run.

Syntax:

DECLARE @Columns NVARCHAR(MAX);
DECLARE @SQL NVARCHAR(MAX);
SET @Columns = ''; 
SELECT @Columns = @Columns + QUOTENAME(ColumnName) + ', '
FROM (SELECT DISTINCT ColumnName FROM TableName) AS Cols;
SET @Columns = LEFT(@Columns, LEN(@Columns) - 1);
SET @SQL = '
SELECT * FROM (
    SELECT PivotColumn, GroupingColumn, AggregateColumn FROM TableName
) AS SourceTable
PIVOT (
    SUM(AggregateColumn) FOR PivotColumn IN (' + @Columns + ')
) AS PivotTable;';
EXEC(@SQL);

Example:

DECLARE @cols NVARCHAR(MAX), @query NVARCHAR(MAX);
SELECT @cols = STRING_AGG(QUOTENAME(Atd_Mon), ',') FROM (SELECT DISTINCT Atd_Mon FROM Em_Atd) AS M;
SET @query = '
SELECT Em_ID, ' + @cols + '
FROM (
    SELECT Em_ID, Atd_Mon, Atd_dy FROM Em_Atd
) AS src
PIVOT (
    MAX(Atd_dy) FOR Atd_Mon IN (' + @cols + ')
) AS pvt;';
EXEC(@query);

Output:

Explanation: Here, the STRING_AGG(QUOTENAME(Atd_Mon), ‘, ‘) extracts the unique region values, and then the constructed query is executed by EXEC().

Method 2: Using sp_executesql for Dynamic PIVOT

In this method, sp_executesql is a system-stored procedure in SQL Server that executes dynamically generated SQL Statements.

Syntax:

DECLARE @Columns NVARCHAR(MAX);
DECLARE @SQL NVARCHAR(MAX);
SET @Columns = ''; 
SELECT @Columns = STRING_AGG(QUOTENAME(ColumnName), ', ')
FROM (SELECT DISTINCT ColumnName FROM TableName) AS Cols;
SET @SQL = N'
SELECT * FROM (
    SELECT PivotColumn, GroupingColumn, AggregateColumn FROM TableName
) AS SourceTable
PIVOT (
    SUM(AggregateColumn) FOR PivotColumn IN (' + @Columns + ')
) AS PivotTable;
EXEC sp_executesql @SQL;

Example:

DECLARE @cols NVARCHAR(MAX), @query NVARCHAR(MAX);
SELECT @cols = STRING_AGG(QUOTENAME(Atd_Mon), ',') FROM (SELECT DISTINCT Atd_Mon FROM Em_Atd) AS M;
SET @query = '
SELECT Em_ID, ' + @cols + '
FROM (
    SELECT Em_ID, Atd_Mon, Atd_dy FROM Em_Atd
) AS src
PIVOT (
    MAX(Atd_dy) FOR Atd_Mon IN (' + @cols + ')
) AS pvt;';
EXEC sp_executesql @query;

Output:

Explanation: Here, the STRING_AGG(QUOTENAME(Atd_Mon), ‘, ‘) extracts distinct Region values to guarantee valid column names.

Dynamic PIVOT for Pivoting Multiple Columns in SQL Server

Dynamic PIVOT queries in SQL Server can also be extended to pivot multiple columns simultaneously, allowing you to reshape your dataset into a matrix-style format. This technique is particularly useful when you need to display grouped metrics side by side, such as total sales and total returns per month, while dynamically generating the required column headers.

Example:

-- Step 1: Declare variables
DECLARE @cols NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);
-- Step 2: Generate column list dynamically
SELECT @cols = STRING_AGG(QUOTENAME(Atd_Mon), ', ')
FROM (SELECT DISTINCT Atd_Mon FROM Em_Atd) AS Months;
-- Step 3: Build dynamic SQL
SET @sql = '
SELECT Em_ID, ' + @cols + '
FROM
(
    SELECT Em_ID, Atd_Mon, Atd_dy
    FROM Em_Atd
) AS SourceTable
PIVOT
(
    MAX(Atd_dy)
    FOR Atd_Mon IN (' + @cols + ')
) AS PivotTable;
';
-- Step 4: Execute dynamic SQL
EXEC sp_executesql @sql;

Output:

Explanation: STRING_AGG(QUOTENAME(…)) safely formats column names like [January], [February]. MAX(Atd_dy) is used because PIVOT requires an aggregate function. The final output shows each Em_ID with attendance (Atd_dy) values for each month.

Debugging Tips for Dynamic PIVOT Queries

1. Print the Dynamic SQL Query
Use PRINT @sql or SELECT @sql to preview the generated SQL before execution. This is essential for debugging dynamic SQL and identifying syntax issues in SQL Server PIVOT queries.

2. Validate Column List with STRING_AGG or STUFF
Check if the column list generated for the PIVOT is well-formed. Missing commas or incorrect formatting often cause errors in Dynamic PIVOT in SQL Server.

3. Use QUOTENAME() to Handle Special Column Names
Always wrap column names with QUOTENAME() to avoid issues with spaces, special characters, or reserved keywords. This ensures compatibility in SQL Server PIVOT query execution.

4. Use TRY…CATCH for Error Handling
Implement a TRY...CATCH block to trap and log runtime errors when executing dynamic SQL. This improves stability and helps with troubleshooting dynamic SQL scripts.

5. Test with a Static PIVOT First
Create a static version of the PIVOT to ensure correctness. Once confirmed, convert it into a dynamic version. This step simplifies debugging SQL Server PIVOT queries and reduces development time.

Performance Comparison of Dynamic PIVOT Methods

Real-World Examples of Dynamic PIVOT in SQL Server

1. Hospital Management

A medical facility would like to track how many patients received services by physician department and by month.

Example:

CREATE TABLE DptVisit (
    VtMnt VARCHAR(10),
    Dt_name NVARCHAR(50),
    Pt_Cnt INT
);
INSERT INTO DptVisit (VtMnt, Dt_name, Pt_Cnt) VALUES
('Jan', 'Cardiology', 150),
('Jan', 'Neurology', 120),
('Feb', 'Cardiology', 170),
('Feb', 'Neurology', 130),
('Mar', 'Cardiology', 160),
('Mar', 'Orthopedics', 110);

SET QUOTED_IDENTIFIER ON;
DECLARE @DeptList NVARCHAR(MAX);
DECLARE @PivotQuery NVARCHAR(MAX);
SELECT @DeptList = STRING_AGG(QUOTENAME(Dt_name), ', ')
FROM (SELECT DISTINCT Dt_name FROM DptVisit) AS D;
SET @PivotQuery = N'
SELECT * FROM (
    SELECT VtMnt, Dt_name, Pt_Cnt FROM DptVisit
) AS VisitData
PIVOT (
    SUM(Pt_Cnt) FOR Dt_name IN (' + @DeptList + ')
) AS MonthlyVisits;';
EXEC sp_executesql @PivotQuery;

Output:

Explanation: Here, this query pivots monthly counts of patient encounters per department, while automatically updating as new departments are added.

Best Practices for Writing Dynamic PIVOT Queries

• To avoid SQL injection and make sure column names are formatted correctly, use QUOTENAME().
• Because sp_executesql supports parameterized queries, which enhance security and performance, it is preferable to EXEC().
• For easier dynamic column list generation, use STRING_AGG() (SQL Server 2017+); for older versions, use STUFF() with FOR XML PATH.
• To troubleshoot issues, always PRINT the dynamic SQL before executing it.

Conclusion 

We can guarantee a versatile, safe, and effective method by utilizing QUOTENAME() for security, sp_executesql for execution, and STRING_AGG() or STUFF() for column list generation. Reliability is further increased by including error handling (TRY…CATCH), indexing for performance, and debugging (PRINT). In this blog, you have gained knowledge of different methods to create Dynamic PIVOT queries in SQL Server.

Take your skills to the next level by enrolling in our SQL Training Course today and gaining hands-on experience. Also, prepare for job interviews with our SQL interview questions, prepared by industry experts.

Below are comprehensive articles designed to introduce you to the essential concepts of SQL, C++, and Backend development.-

Are PostgreSQL column names case sensitive Highlights how quoting identifiers preserves case sensitivity.

SQL injection that gets around mysql_real_escape_string Recommends best practices to secure SQL queries against injections.

How to query MongoDB with LIKE Useful for developers transitioning from SQL to MongoDB.

Why can’t the default constructor be called with empty brackets Highlights compiler behavior related to constructor invocation.

Why does std::getline skip input after a formatted extraction Illustrates correct usage of getline after formatted extractions.

Access local variable from different function in C++ using pointers Teaches safe techniques to pass data between functions.

Assignment operators in C++ Shows how expressions with assignment work in C.

C++ Logical operators Useful for building complex decision-making in code.

C++ Relational operators Highlights how relational expressions return boolean values.

How to Create a Dynamic PIVOT Query in SQL Server – FAQs