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

Without hardcoding the column names, the Dynamic PIVOT Query in SQL Server allows you to transform rows into columns dynamically. These Dynamic PIVOT Queries are used in cases where the column names are not fixed and change over time. In this blog, let us explore the different methods by which we can create a Dynamic PIVOT Query in SQL Server.

Table of Contents:

• What is a Dynamic PIVOT Query?
• Why do we need to create a Dynamic PIVOT Query in SQL Server?
• Advantages of creating a Dynamic PIVOT Query in SQL Server
• Methods 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 Methods for Creating Dynamic PIVOT Query
  • Method 1: Using EXEC() for Dynamic PIVOT
  • Method 2: Using sp_executesql for Dynamic PIVOT
• Performance Comparison of Each Method
• Real-world Examples
• Best Practices
• Conclusion

What is a Dynamic PIVOT Query?

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 Query is useful for reporting and data transformation. 

Why do we need to create a Dynamic PIVOT Query in SQL Server?

Dynamic PIVOT Query has to be created in SQL Server 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 the dynamic SQL, the query can be automatically adjusted to the new data.

Advantages of creating a Dynamic PIVOT Query 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.

Methods 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:

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;

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 Methods for Creating 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.

Performance Comparison of Each Method

Real-world Examples

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

• 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.

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