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 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: values become columns
pivot orders on status using count(*) group by month

-- long again: columns become rows
unpivot report on cancelled, shipped into name status value orders

A DuckDB convenience

PIVOT and UNPIVOT as statements are a DuckDB extension. The portable equivalent is the conditional-aggregation pivot from Lesson 16.1, which every SQL dialect can run.

Schema · Garden ShopTable · orders6 columns · 24 rows

Table · orders

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

6 columns · 24 rows

order_id intcustomer_id intorder_date dateshipped_date datestatus textcoupon_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.

SQL Workbench

query.sqlgarden_shop · SQL engine loading

⌘↵ to run

Expected answer