How to Pivot Data in SQL Server

PIVOT rotates rows into columns — order rows become a customer-per-row, month-per-column report. UNPIVOT does the reverse. This tutorial builds both from a real dataset (results shown are actual query output), then covers the dynamic-pivot pattern for when the column list isn't known in advance.

The data to rotate

Orders across two months:

SELECT c.Name, CONVERT(char(7), o.OrderDate, 126) AS Mo, o.Amount
FROM Orders o JOIN Customers c ON c.CustomerId = o.CustomerId;

Name   Mo       Amount
-----  -------  -------
Asha   2026-06  1200.00
Asha   2026-06   450.00
Rahul  2026-06  2300.00
Meera  2026-06   780.00
Meera  2026-07   320.00
Meera  2026-07   990.00

Six rows; the report wants three — one per customer, months as columns.

PIVOT

SELECT Name,
       ISNULL([2026-06], 0) AS [Jun 2026],
       ISNULL([2026-07], 0) AS [Jul 2026]
FROM (
    SELECT c.Name, CONVERT(char(7), o.OrderDate, 126) AS Mo, o.Amount
    FROM Orders o JOIN Customers c ON c.CustomerId = o.CustomerId
) src
PIVOT (
    SUM(Amount) FOR Mo IN ([2026-06], [2026-07])
) p
ORDER BY Name;

Name   Jun 2026  Jul 2026
-----  --------  --------
Asha   1650.00   0.00
Meera   780.00   1310.00
Rahul  2300.00   0.00

The three moving parts:

1. The source subquery — select exactly the columns that matter: the grouping column (Name), the spreading column (Mo), and the value (Amount). Extra columns silently change the grouping — the most common pivot bug is including OrderId and wondering why nothing aggregates.
2. SUM(Amount) FOR Mo IN (...) — the aggregate to fill cells, the column whose values become column names, and the hard-coded list of those values (bracketed, because 2026-06 isn't a valid identifier).
3. ISNULL(..., 0) — cells with no source rows are NULL; a report usually wants 0.

Any aggregate works: COUNT for order counts, AVG for averages, MAX for latest values.

The CASE alternative

Before PIVOT existed, the same rotation was written with conditional aggregation — and many teams still prefer it because it's more flexible (multiple aggregates side by side, expressions in the conditions):

SELECT c.Name,
       SUM(CASE WHEN MONTH(o.OrderDate) = 6 THEN o.Amount ELSE 0 END) AS [Jun 2026],
       SUM(CASE WHEN MONTH(o.OrderDate) = 7 THEN o.Amount ELSE 0 END) AS [Jul 2026],
       COUNT(*) AS TotalOrders                       -- bonus column PIVOT can't do
FROM Orders o JOIN Customers c ON c.CustomerId = o.CustomerId
GROUP BY c.Name
ORDER BY c.Name;

Same result plus an extra aggregate. Rule of thumb: PIVOT for a single clean rotation, CASE when you need more than one aggregate or computed buckets.

Dynamic pivot — unknown columns

PIVOT's column list is compile-time fixed. When the months (or categories, or years) come from the data, build the statement dynamically:

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

SELECT @cols = STRING_AGG(QUOTENAME(Mo), ', ')
FROM (SELECT DISTINCT CONVERT(char(7), OrderDate, 126) AS Mo FROM Orders) m;

SET @sql = N'
SELECT Name, ' + @cols + N'
FROM (SELECT c.Name, CONVERT(char(7), o.OrderDate, 126) AS Mo, o.Amount
      FROM Orders o JOIN Customers c ON c.CustomerId = o.CustomerId) src
PIVOT (SUM(Amount) FOR Mo IN (' + @cols + N')) p
ORDER BY Name;';

EXEC sp_executesql @sql;

QUOTENAME brackets each value safely (never concatenate raw data into SQL), STRING_AGG builds the list, sp_executesql runs it. This is the standard shape — copy it, change the three column names.

UNPIVOT — columns back to rows

The reverse rotation, for normalizing spreadsheet-shaped tables:

CREATE TABLE #Quarterly (Name NVARCHAR(50), Q1 INT, Q2 INT);
INSERT INTO #Quarterly VALUES ('Asha', 10, 14), ('Rahul', 7, 9);

SELECT Name, Quarter, Orders
FROM #Quarterly
UNPIVOT (Orders FOR Quarter IN (Q1, Q2)) u;

Name   Quarter  Orders
-----  -------  ------
Asha   Q1       10
Asha   Q2       14
Rahul  Q1       7
Rahul  Q2       9

One row per name-quarter — the shape you want before aggregating, joining, or charting. Note UNPIVOT drops rows where the value is NULL; use CROSS APPLY (VALUES ...) instead when NULLs must survive:

SELECT q.Name, v.Quarter, v.Orders
FROM #Quarterly q
CROSS APPLY (VALUES ('Q1', Q1), ('Q2', Q2)) v(Quarter, Orders);

The CROSS APPLY (VALUES) form is also faster on wide tables and composes with more logic — see CROSS APPLY and OUTER APPLY.

When not to pivot in SQL

If the data feeds an application UI or Excel, consider returning normal rows and letting the client pivot — front-end grids and pandas do it natively, and the SQL stays simple and cacheable. Pivot in SQL when the consumer is SQL (a report table, an export) or the row explosion would be expensive to ship.