Dynamic SQL PIVOT Generator — SQL Server
Generate a SQL Server dynamic PIVOT query that discovers its column values at runtime — no hardcoded lists. New values automatically appear as columns without any code changes. Uses STRING_AGG, QUOTENAME, and sp_executesql for safe, production-ready output.
Query Configuration
Generated T-SQL
Examples
Example: Dynamic monthly sales pivot — auto-discovers months
Generates a pivot where the month columns are found by querying the table. Works even as new months are added.
DECLARE @cols NVARCHAR(MAX) = N'';
DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @cols = STRING_AGG(QUOTENAME(Month), ', ')
WITHIN GROUP (ORDER BY MIN(MonthOrder))
FROM (
SELECT DISTINCT Month,
CASE Month
WHEN 'Jan' THEN 1 WHEN 'Feb' THEN 2
WHEN 'Mar' THEN 3 WHEN 'Apr' THEN 4
ELSE 99 END AS MonthOrder
FROM dbo.SalesData
) x;
SET @sql = N'
SELECT [ProductName], ' + @cols + N'
FROM (
SELECT [ProductName], [Month], [Revenue]
FROM dbo.SalesData
) AS [src]
PIVOT (
SUM([Revenue])
FOR [Month] IN (' + @cols + N')
) AS [pvt]
ORDER BY [ProductName];';
EXEC sp_executesql @sql;Tip: print @sql before EXEC to debug. Add PRINT @sql; after SET @sql to see the final query string.
Example: Count employees by role and department — dynamic roles
Employee table where new roles may be added over time. Dynamic PIVOT ensures the new roles appear automatically.
DECLARE @cols NVARCHAR(MAX) = N'';
DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @cols = STRING_AGG(QUOTENAME([Role]), ', ')
FROM (SELECT DISTINCT [Role] FROM dbo.Employees) x;
SET @sql = N'
SELECT [Department], ' + @cols + N'
FROM (
SELECT [Department], [Role], [EmployeeID]
FROM dbo.Employees
) AS [src]
PIVOT (
COUNT([EmployeeID])
FOR [Role] IN (' + @cols + N')
) AS [pvt]
ORDER BY [Department];';
EXEC sp_executesql @sql;What This Tool Does
The Dynamic PIVOT Generator builds a T-SQL stored procedure pattern that constructs a PIVOT query at runtime. Instead of listing the pivot values in the query code, the generated SQL first queries the table to find all distinct values of the column field, builds those into a column list string using STRING_AGG and QUOTENAME, then assembles and executes the final PIVOT query.
This means when new values appear in the column field — a new product, a new region, a new month — they automatically appear as columns in the pivot output without any code changes.
How to Use It
- Enter your Table Name including schema
- Set Row Field, Column Field, Value Field, and Aggregate Function — same as the static PIVOT generator but with column values discovered at runtime
- Optionally filter the data query and the column discovery query separately
- Set an optional NULL replacement value
- Click Generate SQL and copy the output
- Paste into SSMS or Azure Data Studio — run as-is or wrap in a stored procedure
- Debug tip: add
PRINT @sqlbefore the EXEC to see the final SQL string
Static vs Dynamic PIVOT
| Criterion | Static PIVOT | Dynamic PIVOT |
|---|---|---|
| Column values | Hardcoded in query | Queried at runtime |
| New values handled automatically | No | Yes |
| Easier to read and review | Yes | No |
| Performance | Slightly faster | Adds one extra SELECT |
| SQL injection risk | None | Low if using QUOTENAME() |
| Debugging | Straightforward | Print @sql first |
| Best for | Known, stable value lists | Dynamic or growing value sets |
Why Dynamic PIVOT?
Static PIVOT breaks silently when new values appear: the new column simply does not exist in the output. If you are building a report that runs on a schedule — a monthly sales report, a weekly status by project — and new months or projects are added, a static PIVOT requires a code change every time.
Dynamic PIVOT solves this by treating the column value discovery as a runtime step. The QUOTENAME() function wraps each value in square brackets safely, preventing any column value from being interpreted as SQL syntax.
Privacy & Security
All SQL generation runs in your browser. Your table names and schema details are never transmitted to any server. The generated query uses QUOTENAME() on all discovered column values, which is the correct SQL Server defense against SQL injection in dynamic SQL contexts.
Common Problems & Fixes
| Problem | Cause | Fix |
|---|---|---|
| @cols is NULL after SELECT | No rows match the column discovery query | Check the column filter — test the discovery SELECT separately |
| String or binary data would be truncated | Column list exceeds NVARCHAR(MAX) in practice | Filter the column discovery query to reduce the number of distinct values |
| Incorrect syntax near the keyword PIVOT | SQL Server compatibility level too low | SET COMPATIBILITY_LEVEL = 130 or higher on the database |
| sp_executesql fails with parameter error | Passing parameters to a dynamic PIVOT is complex | Use EXEC(@sql) if no parameters needed; restructure for parameterization if required |
| STRING_AGG not recognized | SQL Server version below 2017 | Replace STRING_AGG with STUFF/FOR XML PATH pattern — static PIVOT may be simpler in this case |
💡 If your column values are known and stable, a static PIVOT query is simpler, more readable, and slightly faster to execute. For normalizing a pivoted table back into rows, use the UNPIVOT Generator. To clean up the generated SQL's indentation and keyword casing, paste it into the SQL Formatter.
Dynamic PIVOT Workflow
Dynamic PIVOT connects to the full SQL query builder toolkit:
- Static PIVOT generator — when the column list is known in advance
- UNPIVOT — normalize the wide result back to row format
- Visual Pivot Builder — prototype your pivot with real data before writing dynamic SQL
- Format the dynamic SQL for readability