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?
It is a query action in SQL Server to dynamically generate column names at runtime based on the data in the table instead of hard-coding them. The PIVOT query can be built using ‘EXEC sp_executesql’. 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?
A 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 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
A Dynamic PIVOT query in SQL Server can be generated in various ways depending on the version and use cases, such as STUFF and STRING_AGG.
Before moving on to the methods, let us create an Employee Attendance table and insert some values that we will use for 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;
SET @SQL = '
WITH CTE_Name AS (
SELECT CoB, CoA
FROM SrTab
WHERE Condt
)
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 @Cmn NVARCHAR(MAX);
DECLARE @SQL NVARCHAR(MAX);
SET @Cmn = '';
SELECT @Cmn = @Cmn + QUOTENAME(Colname) + ', '
SET @Cmn = LEFT(@Cmn, LEN(@Cmn) - 1);
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);
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.
| Method | Use Case | Pros | Cons |
| Using STUFF and FOR XML PATH | Used when a CSV has to be generated from the table data | Efficiently handles the dynamic column generation | Due to the FOR XML PATH processing, the performance decreases |
| Using STRING_AGG | Useful when we need to efficiently concatenate multiple row values | Simple and more efficient than XML-based methods | It is not supported in older versions of SQL Server |
| Using Temp Table | Used when intermediate storage is needed before pivoting | Stores the intermediate results for debugging | Memory usage is increased because of temporary storage |
| Using CTE | Used when performing temporary calculations | For better structuring, it can be easily combined with other queries | The performance will be worse than the Temp Table |
Real-world Examples
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, which are prepared by industry experts.
How to Create a Dynamic PIVOT Query in SQL Server – FAQs
Q1. What are the main purposes of dynamic PIVOT queries?
QUOTENAME() for security, STUFF() with FOR XML PATH, STRING_AGG(), and sp_executesql for execution are some of the popular functions.
Q2. How do you prevent SQL injection using a Dynamic PIVOT Query?
To prevent injection hazards and avoid special characters, use QUOTENAME() always while dynamically building column names.
Q3. Do all SQL Server versions support Dynamic PIVOT Queries?
Yes, but STRING_AGG() is available starting with SQL Server 2017+, while STUFF() with FOR XML PATH is required for older versions of SQL Server
Q4. What is the process for debugging a dynamic PIVOT query?
Before execution, use PRINT @SQL to confirm the generated SQL statement.
Q5. Which PIVOT aggregation functions are available?
Depending on the use case, common functions include SUM(), MAX(), MIN(), and COUNT().