How to Convert Rows to Columns Using PIVOT in SQL Server

PIVOT turns the distinct values of one column into column headings and aggregates another column underneath them:

SELECT Product, [Ajay], [Deepak], [Kapil]
FROM (
    SELECT Product, CustomerName, Qty
    FROM dbo.Sales
) AS src
PIVOT (
    SUM(Qty) FOR CustomerName IN ([Ajay], [Deepak], [Kapil])
) AS pvt
ORDER BY Product;

Three things are doing the work: the aggregate (SUM(Qty)), the column whose values become headings (CustomerName), and the list of headings you want (IN (...)). Every other column left in the source query becomes a grouping column.

Sample data

CREATE TABLE dbo.Sales (
    SaleId       int IDENTITY PRIMARY KEY,
    SaleDate     date         NOT NULL,
    CustomerName varchar(50)  NOT NULL,
    Product      varchar(50)  NOT NULL,
    Qty          int          NOT NULL
);

INSERT INTO dbo.Sales (SaleDate, CustomerName, Product, Qty) VALUES
('2026-09-01', 'Ajay',   'Laptop',  2),
('2026-09-03', 'Ajay',   'Mouse',   5),
('2026-09-04', 'Deepak', 'Laptop',  1),
('2026-09-10', 'Deepak', 'Monitor', 3),
('2026-09-12', 'Kapil',  'Mouse',   4),
('2026-09-15', 'Ajay',   'Laptop',  1),
('2026-09-20', 'Kapil',  'Monitor', 2);

The query at the top returns one row per product and one column per customer:

Product   Ajay   Deepak   Kapil
-------   ----   ------   -----
Laptop    3      1        NULL
Monitor   NULL   3        2
Mouse     5      NULL     4

Ajay's two laptop sales (2 and 1) were summed into a single cell.

How to read the syntax

SELECT <grouping columns>, [heading1], [heading2], ...
FROM ( <source query> ) AS src
PIVOT (
    <aggregate>(<value column>)
    FOR <heading column> IN ([heading1], [heading2], ...)
) AS pvt;

• The IN list holds column names, not string literals. Write [Ajay], not 'Ajay'. Brackets are optional for names that are valid identifiers and required for anything else, such as [2026] or [North East].
• The aliases after the source query and after the PIVOT clause are both mandatory.
• A value in the data that is missing from the IN list is silently left out of the result. Nothing warns you.
• The heading column does not have to be a string. Each name in the IN list is converted to the heading column's data type for matching, so an int year column works with FOR SaleYear IN ([2025], [2026]).

The mistake almost everyone makes

PIVOT has no GROUP BY. It groups by every column of its input that is not the value column or the heading column. If you pivot the table directly, the extra columns come along:

SELECT *
FROM dbo.Sales
PIVOT (SUM(Qty) FOR CustomerName IN ([Ajay], [Deepak], [Kapil])) AS pvt;

This groups by SaleId, SaleDate and Product. Because SaleId is unique, you get seven rows, each with one value and two NULLs, and it looks as if PIVOT did nothing.

Always pivot a derived table or CTE that selects exactly three kinds of column: the ones you want to group by, the heading column and the value column.

Replacing NULL with zero

An empty cell is NULL because no rows existed for that combination. Putting ISNULL inside the aggregate does not help, because there is nothing to aggregate. Wrap the output columns instead:

SELECT Product,
       ISNULL([Ajay], 0)   AS Ajay,
       ISNULL([Deepak], 0) AS Deepak,
       ISNULL([Kapil], 0)  AS Kapil
FROM (SELECT Product, CustomerName, Qty FROM dbo.Sales) AS src
PIVOT (SUM(Qty) FOR CustomerName IN ([Ajay], [Deepak], [Kapil])) AS pvt;

When the headings are not known in advance

The IN list must be written into the query text. It cannot be a subquery or a variable. If new customers appear over time, build the statement dynamically:

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

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

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

EXEC sys.sp_executesql @sql;

QUOTENAME wraps each value in brackets and escapes any closing bracket inside it. Do not skip it: the values come from data, and concatenating them unescaped into a statement is a SQL injection hole. STRING_AGG is available from SQL Server 2017. On older versions the same list is built with FOR XML PATH('') and STUFF.

Two limits apply to dynamic pivots. A view or inline function cannot contain one, so put it in a stored procedure. And a result set whose columns change from run to run is awkward for most applications and reporting tools to consume, so consider returning the rows unpivoted and letting the client pivot them. Excel, Power BI and paginated report matrices all do this natively.

The alternative: conditional aggregation

Everything PIVOT does can be written with CASE inside an aggregate and an ordinary GROUP BY:

SELECT Product,
       SUM(CASE WHEN CustomerName = 'Ajay'   THEN Qty ELSE 0 END) AS Ajay,
       SUM(CASE WHEN CustomerName = 'Deepak' THEN Qty ELSE 0 END) AS Deepak,
       SUM(CASE WHEN CustomerName = 'Kapil'  THEN Qty ELSE 0 END) AS Kapil,
       SUM(Qty)                                                   AS Total
FROM dbo.Sales
GROUP BY Product
ORDER BY Product;

Product   Ajay   Deepak   Kapil   Total
-------   ----   ------   -----   -----
Laptop    3      1        0       4
Monitor   0      3        2       5
Mouse     5      0        4       9

Use this form when you need something PIVOT cannot express:

Need	PIVOT	Conditional aggregation
One aggregate over one column	Yes	Yes
Several aggregates at once (sum and count)	No, one aggregate per PIVOT	Yes
A total column alongside the pivoted ones	Needs an outer expression	Yes
Headings based on ranges or expressions	Only by computing them in the source query	Yes
Zero in place of NULL	Needs ISNULL on each output column	Built in with ELSE 0
COUNT(*)	Not allowed	Yes

The two forms usually compile to very similar execution plans, so choose on readability, not speed.

Reversing it with UNPIVOT

UNPIVOT turns columns back into rows:

SELECT Product, CustomerName, Qty
FROM dbo.SalesByCustomer
UNPIVOT (Qty FOR CustomerName IN ([Ajay], [Deepak], [Kapil])) AS u;

Here dbo.SalesByCustomer stands for any table shaped like the pivoted output above. UNPIVOT is not an exact inverse. The pivot aggregated the two Ajay laptop rows into one, and that detail cannot be recovered. UNPIVOT also drops NULL cells, and all the unpivoted columns must have exactly the same data type and length.

Errors and troubleshooting

"Incorrect syntax near ')'" at the end. The alias after the PIVOT (...) clause is missing.

"Invalid column name 'CustomerName'" in the SELECT list. After the pivot, the heading column and the value column no longer exist. Only the grouping columns and the new headings do.

"Invalid column name" for a heading. A name in the SELECT list is not in the IN list.

More rows than expected, mostly NULL. The source includes a column you did not intend to group by. See the section on the common mistake above.

Numeric headings. IN (2025, 2026) is a syntax error. Bracket them: IN ([2025], [2026]).

Performance

A pivot is an aggregation, so it costs what the equivalent GROUP BY would cost. Filter rows in the source query before pivoting, not afterwards, and an index on the grouping column that includes the heading and value columns lets SQL Server aggregate without a sort:

CREATE INDEX IX_Sales_Product ON dbo.Sales (Product) INCLUDE (CustomerName, Qty);

Further reading

• FROM - Using PIVOT and UNPIVOT (Transact-SQL)
• STRING_AGG (Transact-SQL)
• QUOTENAME (Transact-SQL)