SpDev Install free

PIVOT and UNPIVOT in SQL Server, including dynamic PIVOT

Turn rows into columns with PIVOT, the conditional aggregation alternative that's often clearer, a dynamic PIVOT for unknown columns, and UNPIVOT back again.

By Areeb Ahmer, . 4 minute read.

PIVOT turns the values of one column into column headings and aggregates the data under them. Typical use: monthly revenue as one row with a column per category, instead of one row per category and month.

The examples run against the SpDevDemo database, where every product has a Category: Hardware, Software, Services or Training.

The data before pivoting

SELECT YEAR(o.OrderDate) AS Year, p.Category, SUM(l.Quantity * l.UnitPrice) AS Revenue
FROM dbo.Orders AS o
JOIN dbo.OrderLines AS l ON l.OrderId = o.OrderId
JOIN dbo.Products AS p ON p.ProductId = l.ProductId
WHERE o.OrderDate < '2026-01-01'
GROUP BY YEAR(o.OrderDate), p.Category
ORDER BY Year, p.Category;

Twelve rows: three years times four categories. For a report, you want three rows with a column per category.

PIVOT

SELECT Year, Hardware, Software, Services, Training
FROM (
    SELECT YEAR(o.OrderDate) AS Year, p.Category, l.Quantity * l.UnitPrice AS Amount
    FROM dbo.Orders AS o
    JOIN dbo.OrderLines AS l ON l.OrderId = o.OrderId
    JOIN dbo.Products AS p ON p.ProductId = l.ProductId
    WHERE o.OrderDate < '2026-01-01'
) AS src
PIVOT (
    SUM(Amount) FOR Category IN (Hardware, Software, Services, Training)
) AS pvt
ORDER BY Year;
Year  Hardware     Software     Services     Training
----  -----------  -----------  -----------  -----------
2023  20686335.00  20467612.00  20790694.00  20167896.00
2024  20749412.00  20513356.00  20847869.00  20227290.00
2025  20688436.00  20467710.00  20781195.00  20168530.00

Warning: Null value is eliminated by an aggregate or other SET operation.

The warning is expected. Behind the scenes, each column is a SUM over a CASE that is NULL for the other categories, and SUM skips NULLs. It’s harmless here.

How to read it:

  • The derived table src supplies exactly the columns PIVOT needs: the row grouping (Year), the column to spread (Category) and the value to aggregate (Amount).
  • FOR Category IN (...) lists the values that become columns. They’re identifiers, so values with spaces need brackets: [Field Services].
  • Every column of src that isn’t spread or aggregated becomes a grouping column. That’s the main source of PIVOT bugs: an extra column in the source (an OrderId, say) silently turns one row per year into one row per order. Always pivot from a derived table that contains only the columns you need.

The same thing with conditional aggregation

PIVOT is syntax over a pattern you can write yourself:

SELECT YEAR(o.OrderDate) AS Year,
       SUM(CASE WHEN p.Category = 'Hardware' THEN l.Quantity * l.UnitPrice END) AS Hardware,
       SUM(CASE WHEN p.Category = 'Software' THEN l.Quantity * l.UnitPrice END) AS Software,
       SUM(CASE WHEN p.Category = 'Services' THEN l.Quantity * l.UnitPrice END) AS Services,
       SUM(CASE WHEN p.Category = 'Training' THEN l.Quantity * l.UnitPrice END) AS Training,
       SUM(l.Quantity * l.UnitPrice)                                             AS Total
FROM dbo.Orders AS o
JOIN dbo.OrderLines AS l ON l.OrderId = o.OrderId
JOIN dbo.Products AS p ON p.ProductId = l.ProductId
WHERE o.OrderDate < '2026-01-01'
GROUP BY YEAR(o.OrderDate)
ORDER BY Year;

It produces the same numbers, and it’s often the better choice:

  • You can pivot several measures at once (revenue and order count per category), which PIVOT can’t.
  • You can add a total column, or use different aggregates per column.
  • The grouping is explicit in GROUP BY, so extra columns can’t sneak in.

The plans are typically the same, because PIVOT is processed as this kind of aggregation internally; that’s where the warning comes from.

Dynamic PIVOT, when you don’t know the columns

PIVOT needs the column list written in the query. When the values come from data (categories that change, or one column per month), build the query as a string:

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);

SELECT @columns = STRING_AGG(QUOTENAME(Category), N', ') WITHIN GROUP (ORDER BY Category)
FROM (SELECT DISTINCT Category FROM dbo.Products) AS c;

SET @sql = N'
SELECT Year, ' + @columns + N'
FROM (
    SELECT YEAR(o.OrderDate) AS Year, p.Category, l.Quantity * l.UnitPrice AS Amount
    FROM dbo.Orders AS o
    JOIN dbo.OrderLines AS l ON l.OrderId = o.OrderId
    JOIN dbo.Products AS p ON p.ProductId = l.ProductId
    WHERE o.OrderDate < @before
) AS src
PIVOT (SUM(Amount) FOR Category IN (' + @columns + N')) AS pvt
ORDER BY Year;';

EXEC sp_executesql @sql, N'@before date', @before = '2026-01-01';

Two details make this safe:

  • QUOTENAME brackets each value, so a category named x]; DROP TABLE dbo.Orders; -- becomes a harmless column name, not code.
  • Values that aren’t column names (here, the date) are passed as real parameters with sp_executesql, not pasted into the string.

STRING_AGG needs SQL Server 2017 or later. On older versions, STUFF((SELECT ', ' + QUOTENAME(Category) ... FOR XML PATH('')), 1, 2, '') does the same.

The catch with dynamic pivots: the result’s columns change with the data, so code that reads them by name (a report, an ORM) can break when a new category appears. Pivoting in the reporting tool or the application is often more robust.

UNPIVOT: columns back to rows

WITH yearly AS (
    SELECT Year, Hardware, Software, Services, Training
    FROM (
        SELECT YEAR(o.OrderDate) AS Year, p.Category, l.Quantity * l.UnitPrice AS Amount
        FROM dbo.Orders AS o
        JOIN dbo.OrderLines AS l ON l.OrderId = o.OrderId
        JOIN dbo.Products AS p ON p.ProductId = l.ProductId
        WHERE o.OrderDate < '2024-01-01'
    ) AS src
    PIVOT (SUM(Amount) FOR Category IN (Hardware, Software, Services, Training)) AS pvt
)
SELECT Year, Category, Revenue
FROM yearly
UNPIVOT (Revenue FOR Category IN (Hardware, Software, Services, Training)) AS u;

UNPIVOT drops rows whose value is NULL, and all the listed columns must have the same type. CROSS APPLY (VALUES ...) has neither limitation; see CROSS APPLY.

Summary

  • Fixed, known columns: PIVOT, or conditional aggregation if you need several measures or a total.
  • Columns from data: dynamic SQL with QUOTENAME and sp_executesql, or pivot outside SQL.
  • Columns to rows: CROSS APPLY (VALUES ...) for control, UNPIVOT for brevity.