SQL for .NET developers Advanced

PIVOT and UNPIVOT

PIVOT turns row values into columns, such as one column per month, and UNPIVOT does the reverse. This lesson shows both, the conditional aggregation alternative that many developers find easier, and how to pivot when the column list is not known in advance.

Reshaping data
  1. PIVOTRow values become columns: months across the top
  2. Conditional aggregationSUM(CASE WHEN ...) per column; works everywhere
  3. UNPIVOTColumns become rows again
  4. CROSS APPLY VALUESFlexible unpivot with your own labels
  5. Dynamic pivotBuild the column list with QUOTENAME and sp_executesql

Rows into columns

Reports often need a cross-tab: products down the side and months across the top. The data is stored one row per product and month, so it must be pivoted.

SQL
SELECT ProductName, [1] AS Jan, [2] AS Feb, [3] AS Mar
FROM (
    SELECT p.Name AS ProductName, MONTH(o.OrderedAt) AS M, ol.Quantity
    FROM OrderLines ol
    JOIN Orders o   ON o.OrderId = ol.OrderId
    JOIN Products p ON p.ProductId = ol.ProductId
    WHERE o.OrderedAt >= '2026-01-01' AND o.OrderedAt < '2026-04-01'
) AS src
PIVOT (SUM(Quantity) FOR M IN ([1], [2], [3])) AS pvt;

The same with conditional aggregation

SUM(CASE ...) gives the same result, works in every database, and can compute several measures at once. Many teams prefer it for readability.

SQL
SELECT p.Name,
       SUM(CASE WHEN MONTH(o.OrderedAt) = 1 THEN ol.Quantity END) AS Jan,
       SUM(CASE WHEN MONTH(o.OrderedAt) = 2 THEN ol.Quantity END) AS Feb,
       SUM(CASE WHEN MONTH(o.OrderedAt) = 3 THEN ol.Quantity END) AS Mar
FROM OrderLines ol
JOIN Orders o   ON o.OrderId = ol.OrderId
JOIN Products p ON p.ProductId = ol.ProductId
GROUP BY p.Name;

UNPIVOT: columns back into rows

Spreadsheet imports often arrive with one column per month. UNPIVOT (or CROSS APPLY with VALUES) turns them back into rows you can store and query.

SQL
SELECT ProductName, MonthName, Quantity
FROM ImportedSales
CROSS APPLY (VALUES ('Jan', Jan), ('Feb', Feb), ('Mar', Mar)) AS v(MonthName, Quantity)
WHERE Quantity IS NOT NULL;

Unknown columns

PIVOT needs the column list written in the query. When columns depend on the data, such as one per category, build the list with STRING_AGG and QUOTENAME and run it with sp_executesql. QUOTENAME is essential: it brackets each name so data cannot inject SQL.

Often the simpler option is to return rows and pivot in C# or in the reporting tool.

Interview question

How would you pivot when the list of columns is not known in advance?

Build the column list from the data, for example with STRING_AGG(QUOTENAME(Category), ','), put it into a PIVOT query string, and run it with sp_executesql. QUOTENAME protects against SQL injection through the column names. If possible, return plain rows and pivot in the application or report instead, which is simpler to test.

Your progress is saved only in this browser.

Want the full details? Read the official Microsoft documentation for this topic.