PIVOT and UNPIVOT in SQL Server

A grouped query returns data “downwards”: one row per customer and month, for example. But a report usually wants it “sideways”: one row per customer and one column per month, like an Excel pivot table. That rows-to-columns transformation is called pivoting.

SQL Server has the PIVOT operator for it. There is also a standard alternative, using CASE inside an aggregate function, which works in every database and is often easier to read.

The starting point

Sales grouped by customer and month, in vertical format:

We want the same with one column for June, another for July and another for September.

With PIVOT (SQL Server)

SELECT fixedColumns, [value1], [value2], ...
FROM (sourceQuery) AS source
PIVOT (
    aggregateFunction(columnToAggregate)
    FOR columnThatBecomesColumns IN ([value1], [value2], ...)
) AS pivotTable;

Applied to our example:

SELECT name, [6] AS june, [7] AS july, [9] AS september
FROM (
    SELECT C.name,
           MONTH(O.orderDate) AS month,
           O.totalPrice
    FROM Orders O
    INNER JOIN Customers C ON C.customerId = O.customerId
    WHERE O.orderDate >= '2022-06-01'
) AS source
PIVOT (
    SUM(totalPrice)
    FOR month IN ([6], [7], [9])
) AS salesByMonth
ORDER BY name;

Three things to know about PIVOT:

• The IN values have to be written by hand. If a new month appears, the query won’t show it until you add it (or generate the query with dynamic SQL).
• Every column of the source query that is neither the aggregated one nor the FOR one is used for grouping. That’s why the source should contain only the columns you need.
• Cells without data come out as NULL.

Without PIVOT: CASE inside SUM

The same table in standard SQL. Each column is a sum that only counts the rows of its month. This one you can run:

The result is identical to the PIVOT one. Advantages of this approach:

• It works in SQL Server, PostgreSQL, MySQL, Oracle and SQLite.
• Each column can have its own condition or function: one column with July’s sum, another with September’s order count, another with the year’s maximum…
• If you add ELSE 0, empty cells show 0 instead of NULL.

Another example: units by product family

Pivoting doesn’t have to be by date. Units sold to each customer, split into consoles and everything else:

UNPIVOT: from columns to rows

UNPIVOT goes the other way: it turns columns into rows. It’s useful when you receive data in spreadsheet format (one column per month) and need it as a table so you can group or join it.

SELECT name, month, sales
FROM salesByMonth
UNPIVOT (
    sales FOR month IN (june, july, september)
) AS rowsTable;

The standard alternative is a UNION ALL with one query per column:

SELECT name, 'june' AS month, june AS sales FROM salesByMonth WHERE june IS NOT NULL
UNION ALL
SELECT name, 'july', july FROM salesByMonth WHERE july IS NOT NULL
UNION ALL
SELECT name, 'september', september FROM salesByMonth WHERE september IS NOT NULL;

Practice

1. Add a column with each customer’s total to the CASE query.
2. Pivot the number of orders (not the amount) by month.
3. Make empty cells show 0 instead of NULL.