Pivot Tables in SQL Server

Pivoting turns rows into columns: sales data stored as one row per (region, month) becomes a grid with one row per region and one column per month. Unlike MySQL or SQLite, where you build pivots by hand with CASE expressions, T-SQL has a native PIVOT operator -- plus UNPIVOT for the reverse. Both have sharp edges worth knowing before you rely on them.

Sample Schema

CREATE TABLE sales (
    region     varchar(10)  NOT NULL,
    sale_month char(7)      NOT NULL,   -- '2026-01'
    amount     int          NOT NULL
);
 
INSERT INTO sales VALUES
    ('north', '2026-01', 100),
    ('north', '2026-01', 150),
    ('north', '2026-02', 120),
    ('south', '2026-01', 200),
    ('south', '2026-03', 300);

Target output:

region  2026-01  2026-02  2026-03
north   250      120      NULL
south   200      NULL     300

Method 1: Conditional Aggregation (Portable)

The approach that works on every SQL database -- aggregate with a CASE inside:

SELECT
    region,
    SUM(CASE WHEN sale_month = '2026-01' THEN amount END) AS [2026-01],
    SUM(CASE WHEN sale_month = '2026-02' THEN amount END) AS [2026-02],
    SUM(CASE WHEN sale_month = '2026-03' THEN amount END) AS [2026-03]
FROM sales
GROUP BY region;

CASE without an ELSE returns NULL for non-matching rows, and SUM ignores NULLs, so each column aggregates only its month. Missing combinations come back as NULL; wrap in COALESCE(..., 0) if you want zeros.

This is more verbose than PIVOT but has real advantages: you can use different aggregates per column (SUM in one, COUNT in another), reference multiple source columns, and port the query to any database unchanged.

Method 2: The Native PIVOT Operator

SELECT region, [2026-01], [2026-02], [2026-03]
FROM (
    SELECT region, sale_month, amount
    FROM sales
) AS src
PIVOT (
    SUM(amount)
    FOR sale_month IN ([2026-01], [2026-02], [2026-03])
) AS p;

Anatomy:

• SUM(amount) -- the aggregate applied to each cell.
• FOR sale_month -- the column whose values become column headers.
• IN ([2026-01], ...) -- the explicit list of values to turn into columns. These are column identifiers, hence the square brackets, not string quotes.

Why the derived table matters

PIVOT implicitly groups by every column in its input that isn't the aggregate or the spreading column. If you feed it the raw table and the table has extra columns (an id, a timestamp), each distinct combination of those columns becomes its own output row and your pivot "doesn't aggregate." The derived table (src) exists to select exactly three columns: the group-by column, the spreading column, and the value column. This is the #1 PIVOT mistake.

PIVOT limitations

• One aggregate only. Two measures (sum and count per month) means two PIVOTs joined together, or conditional aggregation.
• The IN list is static -- literal column names, no subquery allowed. Unknown months require dynamic SQL (below).
• The aggregate cannot be COUNT(*); use COUNT(column).

For fixed, known columns, PIVOT and conditional aggregation produce comparable plans. Pick whichever reads better to your team.

Method 3: Dynamic Pivot (Unknown Columns)

When the column values aren't known at write time, build the query as a string and execute it. The safe pattern uses QUOTENAME (injection protection) and STRING_AGG (SQL Server 2017+):

DECLARE @cols nvarchar(max), @sql nvarchar(max);
 
SELECT @cols = STRING_AGG(QUOTENAME(sale_month), ', ')
               WITHIN GROUP (ORDER BY sale_month)
FROM (SELECT DISTINCT sale_month FROM sales) AS m;
 
SET @sql = N'
SELECT region, ' + @cols + N'
FROM (SELECT region, sale_month, amount FROM sales) AS src
PIVOT (SUM(amount) FOR sale_month IN (' + @cols + N')) AS p;';
 
EXEC sp_executesql @sql;

Notes:

• QUOTENAME wraps each value in brackets and escapes embedded brackets -- never concatenate raw values into column positions.
• On pre-2017 versions, replace STRING_AGG with the FOR XML PATH concatenation idiom.
• Dynamic SQL result sets have unknown shape at compile time, which means tools, views, and stored procedure result contracts can't bind to them cleanly. Dynamic pivots belong at the final presentation layer, not in the middle of a data pipeline. Often the better architecture is: return tidy rows from SQL and pivot in the reporting tool.

UNPIVOT: Columns Back Into Rows

UNPIVOT rotates the other way -- wide to long:

CREATE TABLE quarterly (
    region varchar(10),
    q1 int, q2 int, q3 int, q4 int
);
INSERT INTO quarterly VALUES ('north', 250, 300, NULL, 410);
 
SELECT region, quarter, amount
FROM quarterly
UNPIVOT (
    amount FOR quarter IN (q1, q2, q3, q4)
) AS u;

region  quarter  amount
north   q1       250
north   q2       300
north   q4       410

Two gotchas:

• NULLs are dropped. q3 is missing from the output entirely. UNPIVOT has no INCLUDE NULLS option (unlike Oracle). If you need the NULL rows, use the CROSS APPLY pattern below.
• All unpivoted columns must have exactly the same type -- including length and nullability quirks. varchar(10) and varchar(20) columns in one UNPIVOT raise error 8167. CAST them to a common type in a derived table first.

CROSS APPLY VALUES: The Flexible Unpivot

The idiomatic T-SQL alternative that keeps NULLs and handles multiple measure columns:

SELECT q.region, v.quarter, v.amount
FROM quarterly AS q
CROSS APPLY (VALUES
    ('q1', q.q1),
    ('q2', q.q2),
    ('q3', q.q3),
    ('q4', q.q4)
) AS v(quarter, amount);

This returns the q3 NULL row. It also scales to unpivoting pairs of columns at once (say, q1_amount + q1_units into one row per quarter) -- something UNPIVOT can't express without joining two UNPIVOTs. On large tables it typically performs at least as well as UNPIVOT. Many T-SQL practitioners skip UNPIVOT entirely and use this pattern everywhere; see CROSS APPLY in the joins guide for how APPLY works.

Which Method to Use

When exploring an unfamiliar dataset to figure out what's worth pivoting, Mako's AI autocomplete can draft the conditional-aggregation skeleton from a plain-language description, which beats typing out a dozen CASE branches by hand.

Mako connects to SQL Server with AI-powered autocomplete. Try it free at mako.ai.