Turning rows into columns

A pivot turns rows into columns: instead of one row per category per year, one row per category with a column for each year. It's what a spreadsheet PivotTable does, and it's how most board reports are laid out.

The portable way is conditional aggregation: one SUM(CASE …) per column.

SELECT p.category,
       SUM(CASE WHEN year(o.order_date) = 2025 THEN oi.quantity * oi.unit_price END) AS y2025,
       SUM(CASE WHEN year(o.order_date) = 2026 THEN oi.quantity * oi.unit_price END) AS y2026
FROM order_items oi
JOIN orders o ON o.id = oi.order_id
JOIN products p ON p.id = oi.product_id
WHERE o.status = 'Completed'
GROUP BY p.category

For each row, the CASE keeps the revenue if it belongs in that column and gives NULL otherwise; SUM ignores the NULLs. (COUNT(CASE … THEN 1 END) counts instead.)

DuckDB-only shortcut: DuckDB has a PIVOT statement that makes the columns for you: PIVOT customers ON customer_type USING COUNT(*) GROUP BY region. SQL Server has a PIVOT clause with a different syntax, and PostgreSQL and MySQL have none, so learn the CASE way first.