How to Pivot Data in SQL Server

To pivot data in SQL Server, use the PIVOT operator to turn distinct row values into columns while aggregating a measure. You supply an aggregate (like SUM(Amount)), a spreading column whose values become the new column headers, and an explicit list of those values. This article builds a small Sales table on SQL Server 2022, writes a classic static pivot, reverses it with UNPIVOT, and then generates a dynamic pivot for when the set of values isn't known ahead of time. Along the way it explains each part of the syntax and when a plain CASE expression is the better tool.

Set up a sample Sales table

Every example below runs against one table. Each row records a product's sales amount for a given year, so we have several rows per product that we want to flatten into one row with a column per year. This "tall" shape is exactly what a normalized database prefers, and it is also exactly what a business user does not want to read. Pivoting bridges that gap: it reshapes the storage-friendly layout into a report-friendly one at query time, without changing how the data is stored.

CREATE TABLE dbo.Sales
(
    SaleId   INT IDENTITY(1,1) PRIMARY KEY,
    Product  VARCHAR(50)  NOT NULL,
    [Year]   INT          NOT NULL,
    Amount   DECIMAL(10,2) NOT NULL
);

INSERT INTO dbo.Sales (Product, [Year], Amount) VALUES
    ('Keyboard', 2023, 1200), ('Keyboard', 2024, 1500), ('Keyboard', 2025, 1750),
    ('Monitor',  2023, 3200), ('Monitor',  2024, 4100), ('Monitor',  2025, 4600),
    ('Mouse',    2023,  800), ('Mouse',    2024,  950), ('Mouse',    2025, 1100);

The raw rows look like this:

Product   Year  Amount
--------  ----  -------
Keyboard  2023  1200.00
Keyboard  2024  1500.00
Keyboard  2025  1750.00
Monitor   2023  3200.00
...

Write a static PIVOT

The static pivot answers the core question: give me one row per product, with a column for each year. You wrap a source query in a subquery, then apply PIVOT with the aggregate and a fixed IN list of the values you want spread across columns. The word "static" here means the list of output columns is fixed in the text of the query — you type each year by hand.

SELECT Product, [2023], [2024], [2025]
FROM
(
    SELECT Product, [Year], Amount
    FROM dbo.Sales
) AS src
PIVOT
(
    SUM(Amount)
    FOR [Year] IN ([2023], [2024], [2025])
) AS pvt
ORDER BY Product;

The result set has one row per product and one column per year:

Product   2023     2024     2025
--------  -------  -------  -------
Keyboard  1200.00  1500.00  1750.00
Monitor   3200.00  4100.00  4600.00
Mouse      800.00   950.00  1100.00

Static PIVOT query aggregating yearly sales into columns in SQL Server 2022

Understand each part of PIVOT

The syntax packs three decisions into a compact block. Read it as three pieces:

The aggregate — SUM(Amount) is applied to every group of rows that lands in a given cell. Because pivoting collapses multiple rows into one, you always need an aggregate (SUM, MAX, COUNT, AVG, and so on).
The spreading column — FOR [Year] names the column whose distinct values become new column headers.
The value list — IN ([2023], [2024], [2025]) is the explicit set of spreading-column values to turn into columns. Values not listed here are silently dropped.

A subtle but important rule: the pivot groups by every column in the source query that is not the aggregate input or the spreading column. Above, only Product remains, so you get one row per product. If you leave SaleId in the inner SELECT, it also becomes a grouping key and the output explodes back into many rows, each with a single populated cell. This is the single most common reason a pivot "doesn't work" — the culprit is almost always an extra column silently participating in the grouping. Select only the columns you actually need in the subquery, and prefer an explicit inner SELECT over SELECT * so nothing sneaks in.

Reverse it with UNPIVOT

UNPIVOT does the opposite: it rotates columns back into rows. This is handy when you receive a wide, spreadsheet-style table and need a normalized shape for reporting or loading. Assume you have a wide table SalesWide(Product, [2023], [2024], [2025]):

SELECT Product, [Year], Amount
FROM SalesWide
UNPIVOT
(
    Amount
    FOR [Year] IN ([2023], [2024], [2025])
) AS unpvt;

That turns each product's three year columns back into three rows:

Product   Year  Amount
--------  ----  -------
Keyboard  2023  1200.00
Keyboard  2024  1500.00
Keyboard  2025  1750.00
...

Note that UNPIVOT skips NULL values by default, so it is not a perfectly lossless inverse of PIVOT. If a product had no sales in a given year, that cell is NULL in the wide table and the corresponding row simply disappears after unpivoting. When you need to preserve those gaps, unpivot with CROSS APPLY and a VALUES clause instead, which lets you keep NULLs explicitly. For most reporting work the default behavior is what you want, since missing combinations rarely need their own rows.

Build a DYNAMIC PIVOT for unknown values

The static pivot hard-codes [2023], [2024], [2025]. That breaks the moment a 2026 row arrives: the new year simply won't appear, and no error warns you. When the value list isn't known at design time — new years, new regions, new categories that appear as the data grows — build the column list at runtime and execute the statement with sp_executesql. STRING_AGG assembles the list and QUOTENAME safely brackets each value to prevent SQL injection and handle awkward characters such as spaces or reserved words.

DECLARE @cols NVARCHAR(MAX);
DECLARE @sql  NVARCHAR(MAX);

SELECT @cols = STRING_AGG(QUOTENAME([Year]), ',')
FROM (SELECT DISTINCT [Year] FROM dbo.Sales) AS y;

SET @sql = N'
SELECT Product, ' + @cols + N'
FROM (SELECT Product, [Year], Amount FROM dbo.Sales) AS src
PIVOT (SUM(Amount) FOR [Year] IN (' + @cols + N')) AS pvt
ORDER BY Product;';

EXEC sp_executesql @sql;

STRING_AGG was introduced in SQL Server 2017 and works well in SQL Server 2022; before that you would concatenate the list with a FOR XML PATH trick. The generated statement is identical to the static version, except the column list now adapts to whatever years exist in the data. Two practical cautions apply to any dynamic SQL: always use QUOTENAME (never raw string concatenation of user-supplied identifiers) so the query stays injection-safe, and print @sql with PRINT @sql while developing so you can inspect the exact statement before it runs.

Consider conditional aggregation instead

PIVOT is not the only way to turn rows into columns. Conditional aggregation with CASE is often clearer and more flexible, especially when you need several measures at once:

SELECT
    Product,
    SUM(CASE WHEN [Year] = 2023 THEN Amount END) AS [2023],
    SUM(CASE WHEN [Year] = 2024 THEN Amount END) AS [2024],
    SUM(CASE WHEN [Year] = 2025 THEN Amount END) AS [2025]
FROM dbo.Sales
GROUP BY Product
ORDER BY Product;

This produces the same result as the static pivot. It reads more naturally to many developers, lets you pivot multiple aggregates in one pass — say total amount and a count of sales side by side — and still supports the dynamic pattern by building the CASE expressions as a string. The query optimizer treats both forms similarly, so the choice is about readability and flexibility rather than raw performance. Choose PIVOT for concise single-measure output, and reach for CASE when you want more control or more than one measure.

Key takeaways

PIVOT turns row values into columns using an aggregate, a spreading column (FOR), and an explicit value list (IN).
The pivot groups by every source column that isn't the aggregate input or the spreading column, so select only what you need.
UNPIVOT reverses the operation, rotating columns back into rows, but it drops NULLs by default.
Use a dynamic pivot with STRING_AGG + QUOTENAME + sp_executesql when the column values aren't known ahead of time.
Conditional aggregation with CASE is a flexible alternative, especially for multiple measures.
All of this runs on SQL Server 2022 with standard T-SQL.

Frequently asked questions

Why do I need an aggregate function in PIVOT?

Pivoting collapses many rows into one cell per column, so SQL Server needs to know how to combine them. Even when only one row maps to a cell, the aggregate (such as SUM or MAX) is still required by the syntax.

How do I pivot when I don't know the column values in advance?

Build the column list dynamically. Use STRING_AGG(QUOTENAME(col), ',') over the distinct values, inject it into a statement string, and run it with sp_executesql. QUOTENAME protects against injection.

What is the difference between PIVOT and UNPIVOT?

PIVOT rotates unique row values into columns while aggregating a measure. UNPIVOT does the reverse, converting a set of columns into rows, which is useful for normalizing wide data.

Is PIVOT better than conditional aggregation with CASE?

Neither is universally better. PIVOT is compact for a single measure, while CASE-based conditional aggregation is clearer and handles multiple measures in one query. Both perform comparably in SQL Server 2022.