You write your first PIVOT, expect three rows back, and get twelve. Every row has one populated column and three NULLs. Nothing in the query says GROUP BY, nothing warns you, and the syntax you copied from the documentation is the syntax you typed.
That is the operator working as designed. PIVOT has a grouping clause you cannot see and cannot write. It infers it from what you left out.
PIVOT and UNPIVOT run on SQL Server 2005 and later on every edition, at compatibility level 90 or higher, which covers every supported build. The dynamic section is the exception: it uses STRING_AGG (SQL Server 2017) with an ordering clause that wants compatibility level 110, and gives the FOR XML PATH form for anything older.
Sample data for the PIVOT examples
CREATE TABLE dbo.SalesFact
(
SalesFactID int IDENTITY(1,1) NOT NULL CONSTRAINT PK_SalesFact PRIMARY KEY,
SalesRep varchar(30) NOT NULL,
Region varchar(20) NOT NULL,
FiscalQuarter char(2) NOT NULL,
Amount decimal(12,2) NOT NULL,
Units int NOT NULL
);
GO
INSERT dbo.SalesFact (SalesRep, Region, FiscalQuarter, Amount, Units) VALUES
('Alvarez','East','Q1', 12000.00, 40),
('Alvarez','East','Q1', 3500.00, 11),
('Alvarez','East','Q2', 9800.00, 33),
('Alvarez','East','Q3', 15200.00, 52),
('Alvarez','East','Q4', 7400.00, 25),
('Brennan','West','Q1', 6100.00, 19),
('Brennan','West','Q2', 14300.00, 47),
('Brennan','West','Q2', 2200.00, 8),
('Brennan','West','Q4', 11900.00, 38),
('Chen', 'East','Q1', 18700.00, 61),
('Chen', 'East','Q3', 4400.00, 14),
('Chen', 'East','Q3', 8800.00, 29);
Twelve rows, three reps, four quarters. Brennan has no Q3. Chen has no Q2 and no Q4. Those gaps matter later.
The quarter here is a stored char(2) so the examples stay about PIVOT. In a real fact table the spreading column is usually derived from a date, and the expression that produces it decides whether the query filtering the fact table still seeks: building the month buckets you are spreading across.
The grouping you did not write
SELECT SalesRep, Q1, Q2, Q3, Q4
FROM dbo.SalesFact
PIVOT (SUM(Amount) FOR FiscalQuarter IN (Q1, Q2, Q3, Q4)) AS p;
Twelve rows. You named two columns inside the PIVOT clause: Amount as the aggregation element and FiscalQuarter as the spreading element. SQL Server took every other column in dbo.SalesFact and made it a grouping column. That list is SalesFactID, SalesRep, Region and Units. SalesFactID is the primary key, so each source row lands in its own group.
Note what the SELECT list did not do. Leaving Region and Units out of the SELECT did not remove them from the grouping. The grouping is decided by the table feeding PIVOT, not by the columns you project.
Itzik Ben-Gan calls this a design trap and puts it first in his list of pivoting pitfalls: "SQL Server determines the grouping element implicitly by elimination based on what you didn't specify as the aggregation and spreading elements." Klaus Aschenbrenner states the consequence: "You can only specify the spreading and aggregation elements! There is no way to define the grouping elements explicitly!"
The fix is to hand PIVOT a table expression containing exactly three columns.
SELECT SalesRep, Q1, Q2, Q3, Q4
FROM
(
SELECT SalesRep, FiscalQuarter, Amount
FROM dbo.SalesFact
) AS src
PIVOT (SUM(Amount) FOR FiscalQuarter IN (Q1, Q2, Q3, Q4)) AS p
ORDER BY SalesRep;
SalesRep Q1 Q2 Q3 Q4
--------- -------- -------- -------- --------
Alvarez 15500.00 9800.00 15200.00 7400.00
Brennan 6100.00 16500.00 NULL 11900.00
Chen 18700.00 NULL 13200.00 NULL
Three rows. Use the derived table or CTE form every time, including when the source query looks like it already has the right shape. The version without it breaks the day somebody adds a column to dbo.SalesFact, and it breaks by returning more rows, not by raising an error.
The aggregate is not optional
SUM(Amount) is required syntax. There is no pass-through mode. If each cell holds one value and you wanted the value rather than a total, you still pick an aggregate, and MAX or MIN is the usual choice because both return the single value unchanged.
Three further restrictions on the aggregate:
- The argument must be a column name.
SUM(Qty * UnitPrice)fails. Compute the product in the derived table and pivot the computed column. COUNT(*)is rejected. Microsoft states it underaggregate_functionon theFROMclause page: "The COUNT(*) system aggregate function isn't allowed." UseCOUNT(SomeColumn), or project a1 AS Cntcolumn in the derived table and useCOUNT(Cnt).- One aggregate per
PIVOTclause. You cannot getSUM(Amount)andSUM(Units)out of a singlePIVOT.
NULLs in the value column are skipped, which Microsoft states on the syntax page: "When aggregate functions are used with PIVOT, the presence of any null values in the value column aren't considered when computing an aggregation." A cell with no matching source rows comes back NULL under SUM, MIN, MAX and AVG, and 0 under COUNT.
Values present in the data but missing from the IN list are dropped. Add a 'Q5' row for one of the existing reps and rerun the query above: the row count stays at three and the amount goes nowhere. Add it for a rep who is not in the table and you get a fourth row with all four quarters NULL, because the rep still forms a group and none of that group's rows land in a named column. PIVOT does not warn about it.
Conditional aggregation does the same work
SELECT SalesRep,
Q1 = SUM(CASE WHEN FiscalQuarter = 'Q1' THEN Amount END),
Q2 = SUM(CASE WHEN FiscalQuarter = 'Q2' THEN Amount END),
Q3 = SUM(CASE WHEN FiscalQuarter = 'Q3' THEN Amount END),
Q4 = SUM(CASE WHEN FiscalQuarter = 'Q4' THEN Amount END)
FROM dbo.SalesFact
GROUP BY SalesRep
ORDER BY SalesRep;
Same three rows, same values, and the GROUP BY is written down where a reviewer can read it.
This is not an alternative implementation. It is the implementation. Craig Freedman walked through PIVOT query plans on the Microsoft query processor blog and showed the optimizer expanding PIVOT into exactly this shape, with a COUNT_BIG alongside each SUM and a Compute Scalar on top to turn an empty count into NULL:
|--Compute Scalar(DEFINE:([Expr1006]=CASE WHEN [Expr1024]=(0) THEN NULL ELSE [Expr1025] END...))
|--Stream Aggregate(DEFINE:([Expr1024]=COUNT_BIG(CASE WHEN [Sales].[Yr]=(2005)...),
[Expr1025]=SUM(CASE WHEN [Sales].[Yr]=(2005)...)...))
|--Table Scan(OBJECT:([Sales]))
One behavioral difference survives the rewrite. Run the CASE version with SET ANSI_WARNINGS ON and you get "Warning: Null value is eliminated by an aggregate or other SET operation." Freedman notes that the PIVOT syntax suppresses that warning. The numbers are identical either way.
Where the CASE form pulls ahead is capability. Multiple aggregates, one pass over the table:
SELECT SalesRep,
Q1_Amt = SUM(CASE WHEN FiscalQuarter = 'Q1' THEN Amount END),
Q1_Qty = SUM(CASE WHEN FiscalQuarter = 'Q1' THEN Units END),
Q2_Amt = SUM(CASE WHEN FiscalQuarter = 'Q2' THEN Amount END),
Q2_Qty = SUM(CASE WHEN FiscalQuarter = 'Q2' THEN Units END),
Q3_Amt = SUM(CASE WHEN FiscalQuarter = 'Q3' THEN Amount END),
Q3_Qty = SUM(CASE WHEN FiscalQuarter = 'Q3' THEN Units END),
Q4_Amt = SUM(CASE WHEN FiscalQuarter = 'Q4' THEN Amount END),
Q4_Qty = SUM(CASE WHEN FiscalQuarter = 'Q4' THEN Units END)
INTO #QuarterlySalesWide
FROM dbo.SalesFact
GROUP BY SalesRep;
The PIVOT equivalent is two PIVOT queries joined on SalesRep, which reads the table twice. The CASE form also takes expressions (SUM(CASE WHEN ... THEN Units * UnitPrice END)), takes different aggregates per column (SUM here, MAX there), and takes a HAVING clause. Reach for PIVOT when you have one aggregate and want less typing. Reach for CASE the moment the requirement grows a second number.
UNPIVOT drops your NULLs
SELECT SalesRep, Q1, Q2, Q3, Q4
INTO #QuarterlySales
FROM (SELECT SalesRep, FiscalQuarter, Amount FROM dbo.SalesFact) AS src
PIVOT (SUM(Amount) FOR FiscalQuarter IN (Q1, Q2, Q3, Q4)) AS p;
SELECT SalesRep, FiscalQuarter, Amount
FROM #QuarterlySales
UNPIVOT (Amount FOR FiscalQuarter IN (Q1, Q2, Q3, Q4)) AS u
ORDER BY SalesRep, FiscalQuarter;
Three rows in, twelve cells, nine rows out. Brennan's Q3 and Chen's Q2 and Q4 are gone. Microsoft says so on the syntax page: "NULL values in the input of UNPIVOT disappear in the output." Ben-Gan's framing of the risk: "The pitfall is when you want to keep the NULLs and you don't even realize that the UNPIVOT operator is designed to remove them."
Count the round trip. Twelve source rows became three pivoted rows became nine unpivoted rows. UNPIVOT reverses neither the aggregation nor the NULL handling.
Two more UNPIVOT constraints. The columns in the IN list must share a data type or be implicitly convertible to one, or you get Msg 8167, The type of column "X" conflicts with the type of other columns specified in the UNPIVOT list. Note the double quotes: that is the message text, not an editorial choice. And the name column comes back as nvarchar(128), which Microsoft documents because UNPIVOT projects column names as values.
CROSS APPLY VALUES instead
SELECT q.SalesRep, v.FiscalQuarter, v.Amount
FROM #QuarterlySales AS q
CROSS APPLY (VALUES
('Q1', q.Q1),
('Q2', q.Q2),
('Q3', q.Q3),
('Q4', q.Q4)
) AS v (FiscalQuarter, Amount)
ORDER BY q.SalesRep, v.FiscalQuarter;
Twelve rows, NULLs included. Add WHERE v.Amount IS NOT NULL and you have the UNPIVOT result. Ben-Gan's assessment: the APPLY form "gives you control of whether you want to keep or remove NULLs. It's more flexible than the UNPIVOT operator in another way, letting you handle multiple unpivoted measures such as both val and qty."
That second clause is the case UNPIVOT cannot reach without a self-join, which is two column groups in one pass:
SELECT q.SalesRep, v.FiscalQuarter, v.Amount, v.Units
FROM #QuarterlySalesWide AS q
CROSS APPLY (VALUES
('Q1', q.Q1_Amt, q.Q1_Qty),
('Q2', q.Q2_Amt, q.Q2_Qty),
('Q3', q.Q3_Amt, q.Q3_Qty),
('Q4', q.Q4_Amt, q.Q4_Qty)
) AS v (FiscalQuarter, Amount, Units);
The row constructor applies data type precedence across the tuples the same way UNPIVOT does, so mismatched types still fail. The difference is that you can write CAST(q.Q3 AS decimal(12,2)) inline and fix it where you see it.
Dynamic PIVOT
The IN list is parsed when the batch compiles. It takes a literal list of identifiers, not a variable and not a subquery. A quarter list you know at write time is fine. A product catalogue, a tag set, or a rolling twelve months is not, and that is the wall most people hit.
One shape worth knowing before you build the string: land the tall result in a #temp table first, read the distinct spreading values from that, and pivot the temp table rather than the base table. You get one consistent set of values for both the column list and the data, statistics on the intermediate result, and a SELECT ... INTO that hits the 1,024-column limit as an error rather than producing a report nobody checks. The tradeoffs are the usual ones: materializing the tall result before you spread it.
On SQL Server 2017 and later. STRING_AGG needs the 2017 binaries, and the WITHIN GROUP (ORDER BY ...) clause needs database compatibility level 110 or higher on top of that, so a legacy database still sitting at compat 100 on a 2017 instance fails on the ordering clause rather than on STRING_AGG itself:
DECLARE @cols nvarchar(max), @sql nvarchar(max);
SELECT @cols = STRING_AGG(CAST(QUOTENAME(q.FiscalQuarter) AS nvarchar(max)), N',')
WITHIN GROUP (ORDER BY q.FiscalQuarter)
FROM (SELECT DISTINCT FiscalQuarter
FROM dbo.SalesFact
WHERE FiscalQuarter IS NOT NULL AND FiscalQuarter <> '') AS q;
IF @cols IS NULL
THROW 50001, 'No pivot columns resolved. Check the source data and QUOTENAME length.', 1;
SET @sql = N'
SELECT SalesRep, ' + @cols + N'
FROM
(
SELECT SalesRep, FiscalQuarter, Amount
FROM dbo.SalesFact
WHERE Region = @Region
) AS src
PIVOT (SUM(Amount) FOR FiscalQuarter IN (' + @cols + N')) AS p
ORDER BY SalesRep;';
EXEC sys.sp_executesql @sql, N'@Region varchar(20)', @Region = 'East';
Four details in that block earn their place.
The CAST to nvarchar(max). STRING_AGG returns nvarchar(4000) when the input is nvarchar(1..4000), and it raises error 9829, "STRING_AGG aggregation result exceeded the limit of 8000 bytes. Use LOB types to avoid result truncation," when the concatenated result outgrows that. QUOTENAME returns nvarchar(258), so without the cast the batch fails once the concatenated list passes 4,000 characters. Four-character names like [Q1] give you hundreds of columns before that happens. Hundred-character product names give you under forty. Aaron Bertrand's fix is the one used here, an explicit conversion inside the aggregate.
WITHIN GROUP (ORDER BY ...). Without it the column order is whatever the plan produces. Report consumers notice when January moves. Check SELECT compatibility_level FROM sys.databases WHERE name = DB_NAME(); before you rely on it.
QUOTENAME on every value. It wraps the value in brackets and doubles any bracket inside it. A category named Widgets] AS x FROM sys.sql_logins;-- becomes the single identifier [Widgets]] AS x FROM sys.sql_logins;--], which fails to resolve as a column rather than executing. Erland Sommarskog's rule on when to apply it: "Every time you insert an object name or a string value from a variable into your SQL string, without any exception."
The IF @cols IS NULL guard. QUOTENAME returns NULL for any input longer than 128 characters, and NULL concatenated into @sql makes the whole statement NULL. Without the guard you get a failure with no useful message attached to the real cause. Column names are sysname, so a pivot value over 128 characters cannot become a column name under any escaping scheme. Truncate it, hash it, or map it to a surrogate label in the derived table.
Note where the user-supplied value went. @Region is a parameter to sp_executesql, not a concatenated string. The pivot column list comes from your own data and goes through QUOTENAME. Anything the caller typed goes through the parameter list. Mixing those two up is how dynamic pivots turn into injection reports.
One permission consequence: ownership chaining covers a stored procedure that reads a table, and it does not cover a dynamic batch inside that procedure. The caller needs SELECT on dbo.SalesFact directly, or the procedure needs EXECUTE AS. Sommarskog covers the mechanics in detail and it surprises people in production.
Before SQL Server 2017
DECLARE @cols nvarchar(max);
SELECT @cols = STUFF((
SELECT N',' + QUOTENAME(q.FiscalQuarter)
FROM (SELECT DISTINCT FiscalQuarter FROM dbo.SalesFact) AS q
ORDER BY q.FiscalQuarter
FOR XML PATH(N''), TYPE).value(N'./text()[1]', N'nvarchar(max)'), 1, 1, N'');
TYPE plus .value('./text()[1]', 'nvarchar(max)') matters. Without it, a category containing & or < comes back as & or < and the generated column name is wrong. The ./text()[1] path is the one Bertrand uses; it tested faster than the shorter alternatives.
What dynamic pivot costs you
Every distinct column list produces a distinct batch text and therefore a distinct plan cache entry. Pivot on quarters and you have one plan. Pivot on the last ninety days of activity and you get a fresh compile and a fresh cache entry each day, forever. Check what yours is doing:
SELECT cp.usecounts, cp.size_in_bytes, cp.cacheobjtype, cp.objtype,
LEFT(st.text, 200) AS batch_start
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE st.text LIKE N'%PIVOT%'
ORDER BY cp.size_in_bytes DESC;
A long list of single-use plans with near-identical text is the signature.
PIVOT performance against conditional aggregation
The plan holds no surprises once you know it is a grouped aggregate. Expect a scan or seek on the source, a Sort on the grouping column, and a Stream Aggregate carrying the conditional expressions, or a Hash Match (Aggregate) with no Sort when the optimizer prefers it.
Two consequences follow.
The Sort is removable. An index keyed on the grouping column with the spreading and value columns available gives the optimizer an ordered stream:
CREATE NONCLUSTERED INDEX IX_SalesFact_Rep_Quarter
ON dbo.SalesFact (SalesRep, FiscalQuarter)
INCLUDE (Amount, Units);
On a three-row result this is noise. On a fact table with forty million rows, the Sort is the memory grant and the spill, and reading which of the two is hurting you is a wait statistics job rather than a PIVOT job: the sort, the grant, and the spill.
Column count multiplies aggregate work per row. Freedman's plan shows PIVOT emitting a COUNT_BIG and a SUM for each pivoted column. Two hundred columns means four hundred aggregate expressions evaluated against every row that reaches the aggregate, whether or not that row contributes to any of them. The scan cost stays flat as you widen the pivot and the aggregate cost climbs with the column count.
A wide pivot over a large fact table is the case where a clustered columnstore index pays, because the Hash Match (Aggregate) can run in batch mode. Check your edition first. Batch mode degree of parallelism is capped at 2 on Standard Edition and 1 on Web and Express, and aggregate pushdown, string predicate pushdown and the SIMD paths are Enterprise only. Erik Darling measured batch mode at DOP 2 losing to row mode at DOP 8. PIVOT itself carries no edition gate and runs on every SKU, but the optimization you are reaching for may not: batch mode is capped before you get there.
PIVOT column limits and data type rules
| Limit | Value | What breaks |
|---|---|---|
Columns per SELECT |
4,096 | The pivot query fails to compile |
| Columns per table or view | 1,024 | SELECT ... INTO from the pivot fails, and so does a view over it |
| Bytes per row | 8,060 | 1,024 decimal(12,2) columns is 9,216 bytes and will not fit |
Bytes per GROUP BY or ORDER BY |
8,060 | Wide composite grouping keys |
| Column name length | 128 (sysname) |
QUOTENAME returns NULL, @sql becomes NULL |
STRING_AGG result without a LOB cast |
8,000 bytes | Error 9829 |
The 4,096 figure is the one people quote for PIVOT and it is the right one for a bare SELECT. The 1,024 figure bites first in practice, because the pivot output usually lands in a table or a view on its way somewhere.
The spreading column itself has a type restriction Microsoft states on the FROM clause page: "pivot_column must be of a type implicitly or explicitly convertible to nvarchar(). This column can't be image or rowversion." Convert in the derived table, and pick the target width with the same care you would give any other column, because an nvarchar conversion in the wrong direction costs a seek elsewhere: the spreading column has to convert to nvarchar.
An empty-string or NULL pivot value has no legal column name at all. QUOTENAME('') returns [] and QUOTENAME(NULL) returns NULL. Filter both out of the distinct list, which the dynamic block above does.
When not to use PIVOT
Push the pivot to the client when any of these hold:
- The column set changes per run and the count runs past a few dozen. You are compiling a new plan per shape to produce a layout.
- The consumer is SSRS, Power BI or Excel. A tablix matrix, a matrix visual and a PivotTable all take tall data and pivot it at render time, with subtotals and drill-down you would otherwise hand-code.
- The result is mostly NULL. Twelve cells for nine facts is a 25% waste; a sparse pivot over hundreds of columns is worse, and the tall form carries no empty cells across the wire.
- You need more than one measure per cell. Ship
SalesRep, FiscalQuarter, Amount, Unitsand let the front end lay it out.
Keep it in T-SQL when the column list is fixed and short, when the result feeds another query rather than a human, or when the consumer is a flat-file export with a contracted header row.
A procedure for writing a PIVOT that works
- Write the
SELECTthat produces the tall result first: one grouping column, one spreading column, one value column. Confirm the row count. - Wrap it in a derived table or CTE exposing only those three columns. Never point
PIVOTat a base table. - Choose the aggregate with the cell contents in mind.
MAXwhen each cell has one value,SUMwhen it has several. If you find yourself wanting two, stop and write conditional aggregation. - Run it and compare the row count against step 1's distinct grouping-column count. A mismatch means a column leaked into the grouping.
- Check the cells that came back NULL. Decide whether NULL,
0orCOALESCEis the contract, and write it down. - Only when the column list is unknown at write time, go dynamic:
QUOTENAMEevery value,CASTinsideSTRING_AGG,WITHIN GROUP (ORDER BY ...), guard against NULL, and pass every caller-supplied value as ansp_executesqlparameter. - Read the plan. If there is a
Sortabove the scan, index for the grouping column and recheck. - Count the output columns. Past a few dozen, and past 1,024 for certain, hand the tall result to the presentation layer instead.
If you would rather hand this off, it is the kind of work I do. See SQL Server DBA services, review client results, or get in touch.