PIVOT, UNPIVOT, and CROSS APPLY in SQL Server: Solving Top-N-Per-Group

Two more tools that solve problems plain JOINs and GROUP BY genuinely can’t express cleanly — reshaping rows into columns, and joining a table to a per-row subquery in a way a normal JOIN’s ON clause structurally cannot do.

PIVOT: Rows Into Columns

SELECT region, [Dana], [Rahul], [Elena]
FROM (SELECT region, salesperson, amount FROM dbo.Sale) src
PIVOT (SUM(amount) FOR salesperson IN ([Dana], [Rahul], [Elena])) AS pvt;

The salesperson values become column headers — this is exactly the shape a spreadsheet-style report needs, and exactly the shape raw relational data never naturally has. The tradeoff: the column list [Dana], [Rahul], [Elena] must be known and hardcoded at query-write time — PIVOT can’t dynamically discover “whatever salespeople happen to exist.” A fully dynamic column list needs dynamic SQL (Chapter 1) to build the PIVOT statement’s IN list at runtime.

UNPIVOT: The Reverse

SELECT region, salesperson, amount
FROM (SELECT region, [Dana], [Rahul], [Elena] FROM dbo.vw_RegionTotals_Wide) src
UNPIVOT (amount FOR salesperson IN ([Dana], [Rahul], [Elena])) AS unpvt;

Turns spreadsheet-shaped, wide data back into normalized, tall rows — genuinely useful when importing an Excel-style export where each salesperson got their own column.

CROSS APPLY: The Top-N-Per-Group Solution

SELECT s.region, top_sale.amount, top_sale.sale_date
FROM (SELECT DISTINCT region FROM dbo.Sale) s
CROSS APPLY (
    SELECT TOP 1 amount, sale_date FROM dbo.Sale WHERE region = s.region ORDER BY amount DESC
) AS top_sale;

Why APPLY, Not JOIN

CROSS APPLY behaves like an INNER JOIN, but the right side can reference the left row directly (as s.region is referenced inside the CROSS APPLY subquery above) — exactly what “top 1 sale per region” needs, and something a standard JOIN’s ON clause is syntactically incapable of expressing. OUTER APPLY is the LEFT JOIN equivalent, keeping left rows even when the applied subquery returns nothing (a region with zero sales still appears, with NULLs for the top-sale columns).

The Window-Function Alternative, Compared

-- The same "top sale per region" answer, using Chapter 6's window functions instead
WITH Ranked AS (
    SELECT region, amount, sale_date,
        ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn
    FROM dbo.Sale
)
SELECT region, amount, sale_date FROM Ranked WHERE rn = 1;

Both approaches give the same result here. As a rule of thumb: reach for CROSS APPLY when the “top N” logic needs to pull in columns or computations that don’t fit neatly into a single window function (a call to a table-valued function per row, for instance); reach for a window function with ROW_NUMBER() = 1 when the whole thing is expressible as ordinary columns from one table, since it’s typically the more efficient, more idiomatic choice for that simpler case.