SQL PIVOT: turn rows into columns, with SQL Server and portable examples

PIVOT in SQL Server turns the values of one column into new columns and aggregates another column into the cells. Sales stored as one row per region and quarter become one row per region with a column for each quarter. Where there is no PIVOT operator, conditional aggregation, one SUM(CASE WHEN ...) per column, gives the same result in any database.

PIVOT in one query

SELECT row columns, [v1], [v2] FROM (SELECT row columns, pivot column, value column FROM table) AS src PIVOT (SUM(value column) FOR pivot column IN ([v1], [v2])) AS p;

Microsoft's PIVOT and UNPIVOT reference describes it as "turning the unique values from one column in the expression into multiple columns in the output", with aggregation run on the remaining values. Three parts matter: the aggregate (SUM here), the FOR column whose values become headers, and the IN list naming the headers you want.

The rows you need

A narrow table with three roles: the row key (Region), the column key (Quarter) and the value (Amount). Feed PIVOT a subquery that selects only those three. Every other column in the source, an invoice ID or a date, becomes an implicit grouping column. If your export is wide, prepare it for analysis first, and derive Quarter from the invoice date using your fiscal calendar, not the calendar year by default.

Worked example: sales by region and quarter

Table dbo.Sales, amounts in USD thousands, with two quarters that hold more than one row:

SELECT Region, [Q1], [Q2], [Q3], [Q4]
FROM (SELECT Region, Quarter, Amount FROM dbo.Sales) AS src
PIVOT (SUM(Amount) FOR Quarter IN ([Q1], [Q2], [Q3], [Q4])) AS p
ORDER BY Region;

Result, with the row and column totals added for the check:

Northeast Q1 is 120 + 30 = 150 and South Q2 is 95 + 10 = 105: PIVOT aggregates duplicate cells, it does not drop them. We ran this query on SQL Server 2022.

The check

The pivot must sum back to the source, the same control total idea as in control totals and identities:

SELECT SUM(Amount) FROM dbo.Sales;   -- 1,035

The row totals give 615 + 420 = 1,035, and the column totals 240 + 245 + 255 + 295 = 1,035. If the pivot comes out short, a quarter is missing from the IN list: drop [Q4] from it and the 295 in Q4 disappears without an error.

The other failure is too many rows. Run the PIVOT on the whole table, with an InvoiceID column included, and every invoice becomes its own group: ten rows, each with one number and three NULLs. That is the most common PIVOT mistake, and it is why the source is a subquery.

Conditional aggregation: the portable way

The same result without PIVOT, in PostgreSQL, MySQL, SQLite, SQL Server and the cloud warehouses:

SELECT Region,
  SUM(CASE WHEN Quarter = 'Q1' THEN Amount END) AS Q1,
  SUM(CASE WHEN Quarter = 'Q2' THEN Amount END) AS Q2,
  SUM(CASE WHEN Quarter = 'Q3' THEN Amount END) AS Q3,
  SUM(CASE WHEN Quarter = 'Q4' THEN Amount END) AS Q4,
  SUM(Amount) AS RowTotal
FROM Sales
GROUP BY Region
ORDER BY Region;

We ran it in SQLite: Northeast returns 150, 140, 155, 170 and 615; South returns 90, 105, 100, 125 and 420. It has two advantages over PIVOT: the row total comes in the same query, and the grouping is explicit, so extra columns cannot slip in.

In PostgreSQL the same columns are shorter with a FILTER clause:

SUM(Amount) FILTER (WHERE Quarter = 'Q1') AS q1

Leave out ELSE. Without it a region with no Q3 sales returns NULL in Q3, which says "no data". ELSE 0 returns 0, which looks like a quarter with sales of zero.

For the wider set of GROUP BY, CASE and window-function patterns, see SQL for data analysis.

Dynamic PIVOT

PIVOT's IN list is fixed when the query is written. When the column values are not known in advance, such as a new quarter or a new product line, build the list at run time in SQL Server:

DECLARE @cols nvarchar(max), @sql nvarchar(max);

SELECT @cols = STRING_AGG(CAST(QUOTENAME(Quarter) AS nvarchar(max)), ', ')
               WITHIN GROUP (ORDER BY Quarter)
FROM (SELECT DISTINCT Quarter FROM dbo.Sales) AS q;

SET @sql = N'SELECT Region, ' + @cols + N'
FROM (SELECT Region, Quarter, Amount FROM dbo.Sales) AS src
PIVOT (SUM(Amount) FOR Quarter IN (' + @cols + N')) AS p;';

EXEC sp_executesql @sql;

@cols becomes [Q1], [Q2], [Q3], [Q4] and the result matches the static query. STRING_AGG needs SQL Server 2017 or later. The CAST to nvarchar(max) matters: without it the result type is capped at 4,000 characters, and a long list fails. QUOTENAME wraps each value in brackets and escapes any bracket inside it, which is what keeps a value from being run as code. It accepts at most 128 characters and returns NULL beyond that.

UNPIVOT: columns back to rows

UNPIVOT turns the quarter columns back into rows:

SELECT Region, Quarter, Amount
FROM dbo.SalesByQuarter
UNPIVOT (Amount FOR Quarter IN ([Q1], [Q2], [Q3], [Q4])) AS u;

Two things differ from the original table. The duplicates are gone: Northeast Q1 comes back as one row of 150, not 120 and 30. And NULLs vanish. Microsoft's reference says "NULL values in the input of UNPIVOT disappear in the output." Add a West row with Q2 empty and UNPIVOT returns 11 rows, not 12.

To keep the NULL rows, use CROSS APPLY with VALUES instead:

SELECT s.Region, x.Quarter, x.Amount
FROM dbo.SalesByQuarter AS s
CROSS APPLY (VALUES ('Q1', s.Q1), ('Q2', s.Q2), ('Q3', s.Q3), ('Q4', s.Q4)) AS x(Quarter, Amount);

That returns all 12 rows, with West Q2 as NULL.

PIVOT in other databases

• Oracle has a PIVOT clause in SELECT, with an IN list like SQL Server's.
• Snowflake has PIVOT and UNPIVOT, and its PIVOT also accepts ANY in place of a fixed list.
• Databricks SQL has a PIVOT clause.
• PostgreSQL has no PIVOT operator. Use FILTER or CASE as above, or the crosstab function from the tablefunc extension.
• MySQL has no PIVOT; use SUM(CASE WHEN ...).

Where it goes wrong

• Extra columns in the source subquery. IDs and dates become grouping columns, and one row per region becomes one row per invoice.
• Values missing from the IN list. They drop out of the result silently, and the pivot total comes out short of the source.
• Dynamic SQL built without QUOTENAME. A column value concatenated straight into the statement can run as code.
• ELSE 0 in conditional aggregation. It turns "no data" into a zero that looks like a real result.
• UNPIVOT dropping NULLs. Row counts fall, and an average over the unpivoted rows changes.

Region-by-quarter tables without SQL

Covirage produces the region-by-quarter layout from your uploaded ledger without SQL, and its deterministic tools check that every row and column sums back to the source control total; the external AI model explains the pattern and never does the arithmetic. See how finance teams use it, and Power Pivot in Excel for the same idea across related tables in a workbook. For the spreadsheet version, see how to make a pivot table in Excel; for the cube tools that did this first, OLAP; for writing the query in plain English, text-to-SQL.