Sets, Pivots & Reshaping Results

Two queries can be correct and still hand back the wrong SHAPE. A chart wants one row per month and metric; a slide wants one column per month; a finance report wants a total row under the detail. Reshaping is the work of getting from one to the other.

This chapter covers the two families that do it. Set operators — UNION, UNION ALL, INTERSECT, EXCEPT — stack and compare whole result sets. Reshaping — pivot, unpivot, and subtotals — moves values between rows and columns.

Everything is taught in the portable form first, because the convenient syntax is the least portable part of SQL. There is no PIVOT in PostgreSQL, no UNPIVOT in most engines, and no GROUPING SETS in SQLite — but conditional aggregation and UNION ALL work everywhere.

set operator

UNION, UNION ALL, INTERSECT or EXCEPT — combines two result sets by comparing whole rows, matched by position rather than by name.

pivot

Moving values from rows into columns: three months of revenue become three columns.

unpivot

The opposite: several metric columns become one row per metric. Harder, because the column names have to be typed as literals.

subtotal row

A row that summarises the rows above it. ROLLUP emits them with NULL in the grouping column, which is why GROUPING() exists.

Both stack one result on top of another. UNION ALL keeps every row. UNION removes duplicates — which means it sorts or hashes the whole combined result to find them.

The columns are matched by POSITION, not by name. If the second branch selects its columns in a different order and the types happen to be compatible, the query runs and every row is silently mislabelled.

UNION ALL is the default you should reach for. Use UNION only when you know duplicates exist and you actually want them removed — and be able to say which rows they were.

select customer_id, placed_at as sold_at, order_total, 'online' as channel
from shop_orders
union all
select customer_id, sold_on, order_total, 'in-store'
from store_orders
order by sold_at, channel;

select customer_id, order_total from shop_orders
union
select customer_id, order_total from store_orders;

INTERSECT keeps the rows present in both branches. EXCEPT keeps the rows in the first branch that are not in the second. Both compare WHOLE rows, and both deduplicate as part of their definition.

That makes them the natural fit whenever the question is genuinely about sets: "which customer-months happened in both channels", "which orders are in the ledger but not in the settlement file".

Direction matters for EXCEPT and is the easiest thing to get backwards. A EXCEPT B is not B EXCEPT A, and both compile.

select customer_id, substr(placed_at, 1, 7) as month
from shop_orders
intersect
select customer_id, substr(sold_on, 1, 7)
from store_orders
order by customer_id, month;

select customer_id, substr(placed_at, 1, 7) as month
from shop_orders
except
select customer_id, substr(sold_on, 1, 7)
from store_orders
order by customer_id, month;

A pivot turns values into column headings. The portable way to write one is a SUM or COUNT wrapped around a CASE: one aggregate per output column, each looking at a different slice of the rows.

The pattern is always the same. The CASE decides which rows contribute to this column; the aggregate collapses them; the GROUP BY decides what a row means.

The ELSE matters more than it looks. Without it the CASE returns NULL, the aggregate skips NULLs, and an empty cell comes back as NULL instead of 0 — which prints as a blank and reads as missing data.

select
  lower(trim(status)) as status,
  round(sum(case when placed_at < '2026-02-01' then order_total else 0 end), 2) as jan,
  round(sum(case when placed_at >= '2026-02-01'
                  and placed_at <  '2026-03-01' then order_total else 0 end), 2) as feb,
  round(sum(case when placed_at >= '2026-03-01' then order_total else 0 end), 2) as mar
from shop_orders
group by lower(trim(status))
order by status;

select
  lower(trim(status)) as status,
  count(*) filter (where placed_at < '2026-02-01') as jan_orders,
  sum(case when placed_at < '2026-02-01' then 1 else 0 end) as jan_orders_portable
from shop_orders
group by lower(trim(status))
order by status;

Several engines have a PIVOT clause. Snowflake, SQL Server, Oracle and Databricks each have one, and no two of them agree on the syntax.

They all do the same thing conditional aggregation does — with one genuine advantage on Snowflake and Databricks, which can take ANY as the value list and discover the columns at run time.

The cost is portability, and a second cost people notice later: a column list discovered at run time is a column list your downstream consumers cannot rely on.

-- Snowflake / Databricks. Not valid on PostgreSQL, SQLite, MySQL or BigQuery.
select *
from (select lower(trim(status)) as status,
             substr(placed_at, 1, 7) as month,
             order_total
      from shop_orders)
pivot (sum(order_total) for month in ('2026-01', '2026-02', '2026-03'))
  as p(status, jan, feb, mar)
order by status;

Unpivoting is the harder direction, and the reason is structural: pivoting reads its column names out of the data, while unpivoting has to write them in as literals.

The portable form is one SELECT per source column, stacked with UNION ALL. Each branch projects the key, a literal naming the metric, and that column as the value.

It is the shape charting libraries and BI tools want — long data, one row per key and measure — which is why it comes up far more often than its reputation suggests.

with monthly as (
  select
    substr(placed_at, 1, 7) as month,
    sum(case when lower(trim(status)) = 'paid'    then order_total else 0 end) as paid_total,
    sum(case when lower(trim(status)) = 'pending' then order_total else 0 end) as pending_total
  from shop_orders
  group by substr(placed_at, 1, 7)
)
select month, 'paid' as metric, round(paid_total, 2) as amount from monthly
union all
select month, 'pending',        round(pending_total, 2)        from monthly
order by month, metric;

A report often needs several levels of aggregation in one result: per status, and a grand total underneath. Running two queries and stapling them together works; GROUPING SETS says it in one clause.

ROLLUP is shorthand for the nested levels — ROLLUP(a, b) means group by (a, b), then by (a), then by nothing at all. CUBE is every combination, which grows fast and is rarely what you want.

All three emit their subtotal rows with NULL in the columns not being grouped, and that is the sharp edge: a NULL that means "all statuses" is indistinguishable from a NULL that means "this status was missing".

-- PostgreSQL, Snowflake, BigQuery, SQL Server, Oracle. Not SQLite or older MySQL.
select
  status,
  count(*)         as orders,
  sum(order_total) as revenue,
  grouping(status) as is_total
from shop_orders
group by rollup (status)
order by is_total, status;

with per_status as (
  select lower(trim(status)) as status,
         count(*)            as orders,
         sum(order_total)    as revenue
  from shop_orders
  group by lower(trim(status))
)
select status as grouping_level, orders, round(revenue, 2) as revenue, 0 as is_total
from per_status
union all
select 'all statuses', sum(orders), round(sum(revenue), 2), 1
from per_status
order by is_total, grouping_level;

Every mistake in this chapter shares a property: the query runs, returns plausible rows, and is wrong in a way that reads as data rather than as a bug.

That is what makes reshaping worth a chapter. The failure mode is never an error message — it is a slide with a blank cell, a total that does not match its own rows, or a set operation that quietly returned nothing.

Five problems on the two channel tables this chapter has been using — `shop_orders` with twelve online orders and `store_orders` with eight in-store sales. Every figure quoted above comes from running these queries against that data.

Predict the shape before you run each one: how many rows, which columns, and what belongs in an empty cell.