Dynamic PIVOT in SQL Server: Auto-Generate Column Lists

Static vs Dynamic PIVOT

A static PIVOT query hardcodes the column values in the IN clause. This works when the set of values is fixed — quarters Q1–Q4, months Jan–Dec, statuses Active/Inactive. But when new values appear in your data — a new product, a new region, a new status — the static query misses them until you update it manually.

A dynamic PIVOT solves this by querying the distinct values at runtime and building the IN clause dynamically using EXEC sp_executesql or EXEC(@sql).

How Dynamic PIVOT Works

The pattern has three steps:

1. Query distinct values — select the distinct values from your pivot column and concatenate them into a comma-separated, bracket-quoted string
2. Build the SQL string — embed that column list into a PIVOT query template as a string variable
3. Execute the SQL string — run the assembled query with EXEC(@sql) or sp_executesql

Using the Generator

Full T-SQL Example

DECLARE @cols   NVARCHAR(MAX);
DECLARE @sql    NVARCHAR(MAX);

-- Step 1: build the column list
SELECT @cols = STRING_AGG(QUOTENAME(Quarter), ',')
FROM (SELECT DISTINCT Quarter FROM sales) AS t;

-- Step 2: build the pivot query
SET @sql = N'
SELECT Region,' + @cols + N'
FROM (
  SELECT Region, Quarter, Revenue
  FROM sales
) AS src
PIVOT (
  SUM(Revenue)
  FOR Quarter IN (' + @cols + N')
) AS pvt
ORDER BY Region;';

-- Step 3: execute
EXEC sp_executesql @sql;

Security: SQL Injection

Tips

• Use STRING_AGG (SQL Server 2017+) or STUFF/FOR XML PATH for older versions to build the column list.
• Add an ORDER BY inside the column-discovery query to control the column order.
• Test with a static PIVOT first — get the logic right, then make it dynamic.
• Dynamic PIVOT results can't be referenced by column name in the outer query because the schema isn't known at parse time.

Frequently Asked Questions