SQL Pivot: Rows to Columns with Explicit Missing-Value Rules

Pivoting is a result shape, not one universal function

A pivot turns category values into output columns while aggregating rows into cells. SQL dialects offer different syntax; conditional aggregation is often the clearest starting point. This guide focuses on the decisions shared by any pivot: output grain, category list, duplicates, missing values, and reconciliation. Its complete executable fixture uses PostgreSQL 16.

Define one cell before writing SQL

The target is one row per product and one column for each month in a fixed three-month period. Multiple legitimate sales within a product-month must be summed. A missing sale row is different from an existing row whose amount is unknown. Product 3 belongs in the report even though it has no sales, so a product dimension supplies the output population.

The fixture includes two January sales for product 1, an unknown January amount for product 2, a missing February for product 1, and an out-of-scope April sale. The three output columns describe months within one chosen reporting period; a real multi-year table must also filter or group by year. Month number alone is not a unique calendar period.

Run conditional aggregation with an explicit population

WITH products(product_id) AS (VALUES (1),(2),(3)),
sales(product_id,month_no,amount) AS (
 VALUES (1,1,10::numeric),(1,1,5),(1,3,9),
        (2,1,NULL),(2,2,7),(2,4,99)
)
SELECT p.product_id,
 SUM(CASE WHEN s.month_no=1 THEN s.amount END) AS january,
 SUM(CASE WHEN s.month_no=2 THEN s.amount END) AS february,
 SUM(CASE WHEN s.month_no=3 THEN s.amount END) AS march,
 COUNT(CASE WHEN s.month_no=1 THEN 1 END) AS january_rows,
 COUNT(CASE WHEN s.month_no=1 THEN s.amount END) AS january_known
FROM products p LEFT JOIN sales s
 ON s.product_id=p.product_id AND s.month_no BETWEEN 1 AND 3
GROUP BY p.product_id ORDER BY p.product_id;

product_id | january | february | march | january_rows | january_known
1 | 15 | NULL | 9 | 2 | 2
2 | NULL | 7 | NULL | 1 | 0
3 | NULL | NULL | NULL | 0 | 0

January rows and January known amounts expose the difference between unknown and absent data. Product 2 has one January observation but zero known amounts. Product 3 has no January observation. Both sums are NULL, but the count columns preserve the evidence needed to interpret them. A display rule can turn genuine no-activity cells into zero only after completeness is established; do not overwrite unknown amounts with zero by default.

Choose the aggregate for the actual fact

SUM correctly combines the 10 and 5 sales for product 1. MAX would instead report 10 and discard valid revenue. Conversely, if rows are duplicated ingestion copies of the same sale, deduplicate on a verified event key before summing. Equal amounts are not evidence of duplicates. For average prices, calculate an agreed weighted numerator and denominator rather than averaging already-averaged cells.

Audit categories outside the fixed report

WITH sales(product_id,month_no,amount) AS (
 VALUES (1,1,10),(1,1,5),(1,3,9),(2,1,NULL),(2,2,7),(2,4,99)
)
SELECT COUNT(*) AS outside_rows,SUM(amount) AS outside_amount
FROM sales WHERE month_no NOT BETWEEN 1 AND 3;

outside_rows | outside_amount
1 | 99

April is intentionally outside this quarter. That exclusion should be visible in reconciliation, not silently blamed on the pivot. The known in-period amounts sum to 31; the unknown January amount remains unresolved. A total of known values is not necessarily the complete revenue total when unknown values exist. Validate allowed category values at ingestion as well as the report’s selected range.

Translate syntax without changing the contract

PostgreSQL supports conditional aggregation and the tablefunc extension’s crosstab; it has no SQL Server-style PIVOT operator. MySQL 8.4 uses expressions such as CASE inside aggregates rather than the SQL Server PIVOT syntax. SQL Server and Oracle have their own PIVOT clauses with different syntax details. A query using one engine’s operator is not a portable shortcut. The examples on this page are executed in PostgreSQL; other-engine syntax should be checked in that engine before use.

For a complete fixed-column PostgreSQL crosstab example, continue with PostgreSQL pivot tables. For genuinely changing columns, use the bounded dynamic pivot export, which defines how the client receives the result.

Keep the result contract stable

A fixed category list makes a report predictable even when a category has no observations. Discovering categories from current data can drop columns on quiet days or introduce new columns unexpectedly. Decide whether the consumer needs a stable table, a versioned export, or long-form rows. More columns can make both SQL and downstream interpretation harder; pivot only the categories needed for the question.

Validate shape, then reconcile measures

Check one row per product, the expected column order, and known source totals. Test missing categories, unknown values, duplicates, and a product with no facts. Inspect representative plans when performance matters; a PIVOT keyword is not inherently faster than an equivalent conditional aggregate. Correctness depends on the data contract and aggregation, not the visual layout.

FAQ

Can I pivot without an extension? Yes; the query above uses ordinary conditional aggregation. Can I recover the original events by unpivoting? Not generally: aggregation loses individual event details. Why omit ELSE 0? It keeps absent or unknown measures visible so a separate policy can decide whether a zero is justified.

Primary references: CASE, aggregate NULL behavior, MySQL CASE, and SQL Server PIVOT.