Most pivot operations in SQL tend to use hardcoded column lists. This is where you explicitly specify every column name in the IN clause. That works great when you know exactly which values will appear in your data. But what happens when those values change? When new categories get added, when you’re working with user-generated data, or when you’re building a query that needs to work across different datasets?
That’s when you might want to consider dynamic PIVOT. Instead of hardcoding column names, you query the data to discover what columns you need, build the PIVOT query as a string, and execute it dynamically. It’s more complex than static PIVOT, but it’s pretty much essential when your column values aren’t predetermined.
When You Need Dynamic PIVOT
Static PIVOT works fine until it doesn’t. If you’re building a report on product categories and a new category gets added next month, your hardcoded query suddenly misses data. If you’re pivoting by date and need to include whatever months exist in the data, you can’t list them all in advance. If different clients have different sets of values, a single static query won’t cover everyone.
Dynamic PIVOT solves these problems by discovering the column values at runtime and constructing the appropriate query on the fly. The tradeoff is added complexity and some security considerations you need to handle carefully.
Simple Example
Let’s start with a simple example that demonstrates how dynamic pivot can work.
Sample Data
We’ll use a dataset tracking server metrics across different data centers. The number of data centers can change as infrastructure grows, making this a good candidate for dynamic pivoting.
Our data might look something like this:
CREATE TABLE ServerMetrics (
MetricDate DATE,
DataCenter VARCHAR(15),
ResponseTimeMs INT
);
INSERT INTO ServerMetrics (MetricDate, DataCenter, ResponseTimeMs)
VALUES
('2024-01-01', 'US-East', 145),
('2024-01-01', 'US-West', 132),
('2024-01-01', 'EU-Central', 156),
('2024-01-01', 'Asia-Pacific', 178),
('2024-01-02', 'US-East', 152),
('2024-01-02', 'US-West', 128),
('2024-01-02', 'EU-Central', 161),
('2024-01-02', 'Asia-Pacific', 182),
('2024-01-03', 'US-East', 148),
('2024-01-03', 'US-West', 135),
('2024-01-03', 'EU-Central', 159),
('2024-01-03', 'Asia-Pacific', 175);
SELECT * FROM ServerMetrics;So our table looks like this:
MetricDate DataCenter ResponseTimeMs
---------------- --------------- --------------
2024-01-01 US-East 145
2024-01-01 US-West 132
2024-01-01 EU-Central 156
2024-01-01 Asia-Pacific 178
2024-01-02 US-East 152
2024-01-02 US-West 128
2024-01-02 EU-Central 161
2024-01-02 Asia-Pacific 182
2024-01-03 US-East 148
2024-01-03 US-West 135
2024-01-03 EU-Central 159
2024-01-03 Asia-Pacific 175
With static PIVOT, you’d need to know all the data center names upfront. With dynamic PIVOT, the query adapts automatically to whatever data centers exist in the table.
Basic Dynamic PIVOT Query
Here’s the basic pattern for a dynamic PIVOT. It queries the distinct values, builds a column list, constructs the full query as a string, and executes it:
DECLARE @columns NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);
-- Get the distinct values and build the column list
SELECT @columns = STRING_AGG(QUOTENAME(DataCenter), ', ')
FROM (SELECT DISTINCT DataCenter FROM ServerMetrics) AS DataCenters;
-- Build the dynamic PIVOT query
SET @sql = N'
SELECT MetricDate, ' + @columns + '
FROM (
SELECT MetricDate, DataCenter, ResponseTimeMs
FROM ServerMetrics
) AS SourceData
PIVOT (
AVG(ResponseTimeMs)
FOR DataCenter IN (' + @columns + ')
) AS PivotTable
ORDER BY MetricDate;';
-- Execute the dynamic query
EXEC sp_executesql @sql;Result:
MetricDate Asia-Pacific EU-Central US-East US-West
---------------- ------------ ----------- ----------- -----------
2024-01-01 178 156 145 132
2024-01-02 182 161 152 128
2024-01-03 175 159 148 135
So we can see that the query successfully pivoted the values in the DataCenter column into separate column headers, with the associated data in the table cells.
The way it works is the STRING_AGG() function (available in SQL Server 2017+) concatenates the distinct data center names into a comma-separated list, with QUOTENAME() adding square brackets around each name. This becomes both the SELECT list and the IN clause of the PIVOT operation.
The result is a query that automatically includes whatever data centers exist in your data, without you having to hardcode them.
Understanding STRING_AGG() and QUOTENAME()
STRING_AGG() simplifies building delimited lists from query results. Before SQL Server 2017, you’d need to use FOR XML PATH with STUFF() to achieve the same thing. If you’re on an older version, here’s the equivalent approach:
SELECT @columns = STUFF((
SELECT ',' + QUOTENAME(DataCenter)
FROM (SELECT DISTINCT DataCenter FROM ServerMetrics) AS DataCenters
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '');This constructs an XML string of all the column names, then uses STUFF() to remove the leading comma. It’s more cryptic than STRING_AGG() but it accomplishes the same goal.
QUOTENAME() is a useful function here, because it properly escapes column names that contain spaces, special characters, or reserved words. If a data center was named US-East (Primary), the QUOTENAME() function ensures it becomes [US-East (Primary)] in your query, which can prevent syntax errors that result from certain characters messing with the operation.
Controlling Column Order
By default, dynamic PIVOT creates columns in whatever order the database returns distinct values, which isn’t always predictable. If column order matters for your output, you can control it explicitly:
DECLARE @columns NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);
-- Order the columns explicitly
SELECT @columns = STRING_AGG(QUOTENAME(DataCenter), ', ') WITHIN GROUP (ORDER BY DataCenter)
FROM (SELECT DISTINCT DataCenter FROM ServerMetrics) AS DataCenters;
SET @sql = N'
SELECT MetricDate, ' + @columns + '
FROM (
SELECT MetricDate, DataCenter, ResponseTimeMs
FROM ServerMetrics
) AS SourceData
PIVOT (
AVG(ResponseTimeMs)
FOR DataCenter IN (' + @columns + ')
) AS PivotTable
ORDER BY MetricDate DESC;';
EXEC sp_executesql @sql;Result:
MetricDate Asia-Pacific EU-Central US-East US-West
---------------- ------------ ----------- ----------- -----------
2024-01-03 175 159 148 135
2024-01-02 182 161 152 128
2024-01-01 178 156 145 132
The WITHIN GROUP (ORDER BY ...) clause sorts the column list alphabetically. You can order by any criteria that makes sense – alphabetically, by a custom sort order in a lookup table, or by some derived value.
Filtering Which Columns to Include
Sometimes you don’t want every distinct value as a column. Maybe you only want recent data centers, or you want to exclude test environments, or you need to limit the column count.
Here’s an example of filtering our query to just US data centers:
DECLARE @columns NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);
-- Only include data centers that start with 'US-'
SELECT @columns = STRING_AGG(QUOTENAME(DataCenter), ', ')
FROM (
SELECT DISTINCT DataCenter
FROM ServerMetrics
WHERE DataCenter LIKE 'US-%'
) AS DataCenters;
SET @sql = N'
SELECT MetricDate, ' + @columns + '
FROM (
SELECT MetricDate, DataCenter, ResponseTimeMs
FROM ServerMetrics
WHERE DataCenter LIKE ''US-%''
) AS SourceData
PIVOT (
AVG(ResponseTimeMs)
FOR DataCenter IN (' + @columns + ')
) AS PivotTable
ORDER BY MetricDate;';
EXEC sp_executesql @sql;Result:
MetricDate US-East US-West
---------------- ----------- -----------
2024-01-01 145 132
2024-01-02 152 128
2024-01-03 148 135
Notice that the WHERE clause appears twice (once when building the column list and once in the actual PIVOT query). Also notice the doubled single quotes in the dynamic SQL string ('') which escapes them properly.
This pattern lets you create filtered pivot reports without hardcoding which specific values should appear.
Using Parameters with sp_executesql
When your dynamic query needs to accept parameters (like date ranges or other filter criteria), use parameterized sp_executesql instead of concatenating values directly into the SQL string.
For example:
DECLARE @columns NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);
DECLARE @startDate DATE = '2024-01-02';
DECLARE @endDate DATE = '2024-01-03';
SELECT @columns = STRING_AGG(QUOTENAME(DataCenter), ', ')
FROM (SELECT DISTINCT DataCenter FROM ServerMetrics) AS DataCenters;
SET @sql = N'
SELECT MetricDate, ' + @columns + '
FROM (
SELECT MetricDate, DataCenter, ResponseTimeMs
FROM ServerMetrics
WHERE MetricDate BETWEEN @StartDate AND @EndDate
) AS SourceData
PIVOT (
AVG(ResponseTimeMs)
FOR DataCenter IN (' + @columns + ')
) AS PivotTable
ORDER BY MetricDate;';
EXEC sp_executesql @sql,
N'@StartDate DATE, @EndDate DATE',
@StartDate = @startDate,
@EndDate = @endDate;Result:
MetricDate Asia-Pacific EU-Central US-East US-West
---------------- ------------ ----------- ----------- -----------
2024-01-02 182 161 152 128
2024-01-03 175 159 148 135
The sp_executesql call includes a parameter definition string (N'@StartDate DATE, @EndDate DATE') and then passes the actual parameter values. This approach prevents SQL injection and often improves query plan reuse.
Security Considerations
Dynamic SQL opens the door to SQL injection if you’re not careful. The examples here are safe because they build column lists from actual database values using QUOTENAME(). But if you ever concatenate user input directly into dynamic SQL, you’re asking for trouble.
Never do this:
-- DANGEROUS: Don't concatenate user input
DECLARE @userInput VARCHAR(50) = 'US-East'; -- Imagine this came from a web form
SET @sql = 'SELECT * FROM ServerMetrics WHERE DataCenter = ''' + @userInput + '''';Always use parameters for user-provided values:
-- SAFE: Use parameterized queries
DECLARE @userInput VARCHAR(50) = 'US-East';
SET @sql = 'SELECT * FROM ServerMetrics WHERE DataCenter = @DataCenter';
EXEC sp_executesql @sql, N'@DataCenter VARCHAR(50)', @DataCenter = @userInput;For the column list itself, since you’re pulling from actual table data and using QUOTENAME(), you’re protected. But stay vigilant about any user-controlled values that might enter your dynamic SQL.
Debugging Dynamic SQL
When your dynamic PIVOT query doesn’t work as expected, the first debugging step is to print the generated SQL before executing it:
DECLARE @columns NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);
SELECT @columns = STRING_AGG(QUOTENAME(DataCenter), ', ')
FROM (SELECT DISTINCT DataCenter FROM ServerMetrics) AS DataCenters;
SET @sql = N'
SELECT MetricDate, ' + @columns + '
FROM (
SELECT MetricDate, DataCenter, ResponseTimeMs
FROM ServerMetrics
) AS SourceData
PIVOT (
AVG(ResponseTimeMs)
FOR DataCenter IN (' + @columns + ')
) AS PivotTable
ORDER BY MetricDate;';
-- Print the SQL to see what you're actually executing
PRINT @sql;Output:
SELECT MetricDate, [Asia-Pacific], [EU-Central], [US-East], [US-West]
FROM (
SELECT MetricDate, DataCenter, ResponseTimeMs
FROM ServerMetrics
) AS SourceData
PIVOT (
AVG(ResponseTimeMs)
FOR DataCenter IN ([Asia-Pacific], [EU-Central], [US-East], [US-West])
) AS PivotTable
ORDER BY MetricDate;
The PRINT statement shows you the complete query string. You can copy that output and run it directly to troubleshoot syntax errors or logic issues. If the string is longer than 8000 characters (PRINT‘s limit), you’ll need to print it in chunks or use a longer output method.
Performance Considerations
Dynamic PIVOT has the same performance characteristics as static PIVOT for the actual data transformation (SQL Server’s query optimizer treats the executed statement normally). The overhead comes from constructing the query string and the fact that the execution plan can’t be reused as effectively across different column sets.
If you’re running the same dynamic PIVOT repeatedly with the same column values, consider caching the column list or building the query once and reusing it. For one-off reports or queries where the columns genuinely change between executions, the overhead becomes less of an issue.
Also be aware that if your dynamic query generates an enormous number of columns (perhaps hundreds or thousands) you might hit practical limits in terms of result set width or client application display capabilities, even if SQL Server can technically handle it.
When to Avoid Dynamic PIVOT
Dynamic SQL adds complexity and makes your code harder to maintain and debug. If you can reasonably enumerate your columns in advance, static PIVOT is usually better. If your column values are truly unbounded or change constantly, consider whether pivoting is even the right approach. Sometimes keeping data in a normalized format and letting the reporting tool handle the transformation is cleaner.
Dynamic PIVOT also complicates things if you need to reference the output in subsequent queries, since you don’t know the column names at development time. You’d need more dynamic SQL to work with the results, which can quickly become unwieldy.
Quick Article Summary
Dynamic PIVOT gives you the flexibility to handle unknown column values at runtime, adapting automatically to whatever data exists in your tables. It requires more careful coding than static PIVOT, especially around security and debugging, but it can be a very handy technique for those times when your data structure isn’t fixed.