Dynamic PIVOT: Building Report Columns From the Data

The report works until a new month arrives, then that month disappears. A dynamic PIVOT builds its output columns from the selected data instead of a hard-coded list. Keep values parameterized and identifiers quoted so the flexible report remains safe to run.

Start With a Small Sales Set

A pivot converts category values into columns and aggregates a measure. Use a narrow source containing only the row key, category, and amount. Extra source columns can become unintended grouping columns and split a salesperson into several rows. I check the source grain before building dynamic SQL. Most mysterious pivot totals begin with a source that is too wide.

CREATE TABLE #Sales
(
    Salesperson varchar(30) NOT NULL,
    MonthStart date NOT NULL,
    Amount decimal(12,2) NOT NULL
);
INSERT #Sales VALUES
('Ava','20250101',100),('Ava','20250201',120),
('Ben','20250101',80),('Ben','20250301',90);

Run the examples in the same session. The temporary table remains visible to sp_executesql. In a permanent design, enforce the desired month representation and decide whether duplicate source rows are legitimate sales or a loading defect. SUM will aggregate either kind without asking.

See the Static Column List

The IN list of a static PIVOT names the columns the result will expose. It does not discover new categories. If March appears in the source but only January and February appear in the list, the report does not create March automatically. That is predictable SQL behavior and an easy reporting mistake.

SELECT Salesperson,[2025-01],[2025-02]
FROM
(
    SELECT Salesperson,CONVERT(char(7),MonthStart,126) AS MonthKey,Amount
    FROM #Sales
) AS src
PIVOT
(SUM(Amount) FOR MonthKey IN ([2025-01],[2025-02])) AS p;

Use a static list when the result schema is intentionally fixed. A scheduled integration that expects twelve known columns can prefer that contract. Dynamic output is useful for exploration and flexible reports, but it can surprise clients that bind fields by a fixed name or ordinal.

Derive Ordered and Quoted Identifiers

Build the category list from distinct values inside the selected date range. Format month names as year-month keys so lexical order follows time. Apply QUOTENAME to each identifier and cast its input to STRING_AGG as nvarchar(max). That avoids the aggregate's shorter fixed-length result limit when the list grows.

QUOTENAME is for identifiers, not data values. It returns NULL for input beyond its identifier-length allowance, so validate free-form categories before aggregation. The controlled seven-character month key avoids that problem here. Which categories should exist when the selected range has no sales? A calendar table is the right source when empty months must still appear.

Run the Dynamic PIVOT as One Parameterized Statement

The complete block builds two lists: the PIVOT IN list and the outer zero-filled expressions. It passes the date range as typed parameters. Only the validated identifiers are concatenated into SQL text. The NULL-list check handles an empty source explicitly instead of executing an invalid pivot. On the sample rows it returns three month columns, with zeros where a salesperson had no sale.

DECLARE @from date='20250101',@to date='20250401';
DECLARE @cols nvarchar(max),@select_cols nvarchar(max),@sql nvarchar(max);
WITH months AS
(
    SELECT DISTINCT CONVERT(char(7),MonthStart,126) AS MonthKey
    FROM #Sales WHERE MonthStart>=@from AND MonthStart<@to
)
SELECT @cols=STRING_AGG(CONVERT(nvarchar(max),QUOTENAME(MonthKey)),N',')
                   WITHIN GROUP (ORDER BY MonthKey),
       @select_cols=STRING_AGG(CONVERT(nvarchar(max),
           N'COALESCE('+QUOTENAME(MonthKey)+N',0) AS '+QUOTENAME(MonthKey)),N',')
                   WITHIN GROUP (ORDER BY MonthKey)
FROM months;
IF @cols IS NULL THROW 50000,'No months match this range.',1;
SET @sql=N'SELECT Salesperson,'+@select_cols+N'
FROM
(
    SELECT Salesperson,CONVERT(char(7),MonthStart,126) AS MonthKey,Amount
    FROM #Sales WHERE MonthStart>=@from AND MonthStart<@to
) AS src
PIVOT (SUM(Amount) FOR MonthKey IN ('+@cols+N')) AS p
ORDER BY Salesperson;';
EXEC sys.sp_executesql @sql,N'@from date,@to date',@from=@from,@to=@to;

Decide What Zero Means

PIVOT returns NULL when a group has no value for a listed category. COALESCE in the outer SELECT displays zero for that missing aggregate. That is a reporting rule, not proof that the source recorded a zero sale. Preserve the difference if missing data needs investigation. An entirely absent salesperson also needs an outer join to the reporting population.

I review zero filling with the report owner. A dashboard can turn a failed load into reassuring zeros if it treats all missing values alike. Validate load completeness separately. Keep NULL when it communicates unknown data, and use zero only when the business definition says no matching sales means zero.

Compare Conditional Aggregation

For a fixed small category list, conditional aggregation is clear and flexible. It can calculate several measures without multiple pivots. A dynamic version still needs safe identifier construction, but the fixed example below is easy to inspect. Compare plans and logical reads on your real source before choosing one syntax for performance reasons.

SELECT Salesperson,
       SUM(CASE WHEN MonthStart='20250101' THEN Amount ELSE 0 END) AS January,
       SUM(CASE WHEN MonthStart='20250201' THEN Amount ELSE 0 END) AS February,
       SUM(CASE WHEN MonthStart='20250301' THEN Amount ELSE 0 END) AS March
FROM #Sales
GROUP BY Salesperson;

The amount type and ELSE value participate in result-type rules. Check totals and decimal scale. I use the conditional form as a correctness comparison for a small test range. The two versions should agree on the same source grain and zero policy.

Put a Width Limit on the Dynamic PIVOT

SQL Server allows up to 4,096 columns in a SELECT, including nonpivot columns. A regular persisted table has a different column limit. Your reporting client will become unpleasant well before an enormous grid reaches the engine limit. Set a smaller application limit and reject an overwide selection with a useful message.

A hundred product categories and many periods can explode output width. Return rows for the client to pivot when a rectangular SQL result becomes unwieldy. Check memory grants, spills, source indexes, and dynamic plan variations. Index the range and grouping columns according to the source workload rather than adding an index for every output heading.

Keep the Dynamic PIVOT Safe and Predictable

Limit the category domain before building identifiers. A category containing a closing bracket is handled by QUOTENAME, but a category beyond its length limit produces NULL. STRING_AGG ignores NULL inputs, which can silently omit that category. Detect and reject it before aggregation. Also reserve names that collide with the row-key heading, such as Salesperson. Quoting protects syntax; it does not create a sensible result schema.

For reproducible exports, save the selected date range and ordered category list with the report. Two executions against changing data can expose different columns. If an integration expects the same fields each time, derive columns from an approved calendar or category dimension. A flexible screen and a stable file interface have different contracts.

Never concatenate an untrusted WHERE value, ORDER BY fragment, or table name just because the month identifiers are quoted. Parameters protect values. An allowlist protects permitted sort and object choices. Inspect the generated SQL in a test environment and verify dates, category order, totals, and missing categories.

I keep tests for a new month, an empty range, duplicate categories, and a category containing a closing bracket when free-form names are supported. Flexibility adds contracts rather than removing them. The query should explain how columns are chosen and what the consuming client can expect.

Related reading on this blog: Exploring PIVOT and UNPIVOT and Input and Output Parameter for Dynamic SQL: Simple Example.

A dynamic pivot is not a fixed report schema, it is a controlled result shape derived from selected categories.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.