Lesson 16.6

Reshaping Rows and Columns with PIVOT and UNPIVOT in SQL

PIVOT turns row values into columns for a crosstab; UNPIVOT flips those columns back into rows.

In Lesson 16.1 · Pivoting with conditional aggregation you built a crosstab by hand with count(*) filter (where ...). DuckDB also has a dedicated PIVOT statement that turns the values in one column into headers automatically. No need to name each status yourself.

The shape is PIVOT source ON header_column USING aggregate GROUP BY kept_rows. Its opposite, UNPIVOT, folds several columns back into name/value rows, which is handy for tidying a wide spreadsheet into long form.

Pattern

-- wide: each status value becomes a column
create or replace temp table status_by_customer as
pivot orders on status using count(*) group by customer_id;

-- long again: those columns fold back into name/value rows
unpivot status_by_customer
on cancelled, pending, processing, shipped
into name status value orders;

A DuckDB convenience

PIVOT and UNPIVOT as standalone statements are DuckDB's own syntax. SQL Server, Oracle, Snowflake, and BigQuery have PIVOT and UNPIVOT too, but as operators inside FROM, each spelled a little differently, and PostgreSQL and MySQL have neither. The portable alternative is conditional aggregation with CASE expressions, as in Lesson 16.1.

Schema · Garden Shop Table · orders 6 columns · 24 rows

Table · orders

One row per order. Unshipped orders have a null shipped_date.

6 columns · 24 rows

order_id int customer_id int order_date date shipped_date date status text coupon_code text

Your task

Build a crosstab of order counts by month and status: one row per month and one column per status (cancelled, pending, processing, shipped). Start from the long-form starter query and pivot it.