Someone asked me a reporting question the other day. In Excel I'd answer it with a pivot table without thinking. I didn't know how to do it in SQL, so I researched how.

Say the data lives in two tables: a products table, and a sales table that points at it by product_id.

\d products
              Table "public.products"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 id     | integer |           | not null |
 name   | text    |           | not null |
Indexes:
    "products_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "sales" CONSTRAINT "sales_product_id_fkey" FOREIGN KEY (product_id) REFERENCES products(id)

\d sales
                 Table "public.sales"
   Column   |  Type   | Collation | Nullable | Default
------------+---------+-----------+----------+---------
 sale_date  | date    |           |          |
 product_id | integer |           |          |
 amount     | numeric |           |          |
Foreign-key constraints:
    "sales_product_id_fkey" FOREIGN KEY (product_id) REFERENCES products(id)

Pivoting is turning row values into columns. Joined, that's the long shape we pivot:

-- query:
SELECT s.sale_date, p.name AS product, s.amount
FROM sales s
JOIN products p ON p.id = s.product_id
ORDER BY s.sale_date, p.name;

-- result:
 sale_date  | product | amount
------------+---------+--------
 2026-01-01 | Apples  |    100
 2026-01-01 | Bananas |     30
 2026-01-01 | Oranges |     50
 2026-02-01 | Apples  |    200
 2026-02-01 | Bananas |     60
 2026-02-01 | Oranges |     75

Into this, one row per date, one column per product:

 sale_date  | apples | oranges | bananas
------------+--------+---------+---------
 2026-01-01 |    100 |      50 |      30
 2026-02-01 |    200 |      75 |      60

-- query:
SELECT s.sale_date,
  SUM(CASE WHEN p.name = 'Apples'  THEN s.amount ELSE 0 END) AS apples,
  SUM(CASE WHEN p.name = 'Oranges' THEN s.amount ELSE 0 END) AS oranges,
  SUM(CASE WHEN p.name = 'Bananas' THEN s.amount ELSE 0 END) AS bananas
FROM sales s
JOIN products p ON p.id = s.product_id
GROUP BY s.sale_date
ORDER BY s.sale_date;

-- result:
 sale_date  | apples | oranges | bananas
------------+--------+---------+---------
 2026-01-01 |    100 |      50 |      30
 2026-02-01 |    200 |      75 |      60

The explicit version, and the one I grok best: one SUM(CASE WHEN ...) per column, GROUP BY the date.

-- query:
SELECT s.sale_date,
  SUM(s.amount) FILTER (WHERE p.name = 'Apples')  AS apples,
  SUM(s.amount) FILTER (WHERE p.name = 'Oranges') AS oranges,
  SUM(s.amount) FILTER (WHERE p.name = 'Bananas') AS bananas
FROM sales s
JOIN products p ON p.id = s.product_id
GROUP BY s.sale_date
ORDER BY s.sale_date;

-- result:
 sale_date  | apples | oranges | bananas
------------+--------+---------+---------
 2026-01-01 |    100 |      50 |      30
 2026-02-01 |    200 |      75 |      60

Same result, shorter. Postgres's FILTER clause. Less to read. I still reach for CASE WHEN.

-- query:
CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT * FROM crosstab(
  $$ SELECT s.sale_date, p.name, SUM(s.amount)
     FROM sales s JOIN products p ON p.id = s.product_id
     GROUP BY s.sale_date, p.name ORDER BY 1, 2 $$
) AS ct(sale_date date, apples numeric, oranges numeric, bananas numeric);

-- result:
 sale_date  | apples | oranges | bananas
------------+--------+---------+---------
 2026-01-01 |    100 |      30 |      50
 2026-02-01 |    200 |      60 |      75

Runs without an error. Also wrong: oranges is showing Bananas, bananas is showing Oranges. crosstab fills columns in the order the categories sort in the inner query (alphabetical by product name: Apples, Bananas, Oranges), not by the names in AS ct(...).

-- query:
SELECT * FROM crosstab(
  $$ SELECT s.sale_date, p.name, SUM(s.amount)
     FROM sales s JOIN products p ON p.id = s.product_id
     GROUP BY s.sale_date, p.name ORDER BY 1, 2 $$,
  $$ VALUES ('Apples'), ('Oranges'), ('Bananas') $$
) AS ct(sale_date date, apples numeric, oranges numeric, bananas numeric);

-- result:
 sale_date  | apples | oranges | bananas
------------+--------+---------+---------
 2026-01-01 |    100 |      50 |      30
 2026-02-01 |    200 |      75 |      60

Two-arg form. The second query pins the category order. Now it's right. If you use crosstab, use this form.

All of these need the columns hard-coded up front ('Apples', 'Oranges'...). Postgres won't grow columns from the data the way Excel does.

CASE WHEN covers almost everything and reads clearest to me. FILTER when you want it shorter (arguably cleaner to read). I found crosstab to be hard to follow.

Want to run these yourself? Seed the two tables:

CREATE TABLE IF NOT EXISTS products (
  id   int PRIMARY KEY,
  name text NOT NULL
);

CREATE TABLE IF NOT EXISTS sales (
  sale_date  date,
  product_id int REFERENCES products(id),
  amount     numeric
);

INSERT INTO products (id, name) VALUES
  (1, 'Apples'),
  (2, 'Oranges'),
  (3, 'Bananas');

INSERT INTO sales (sale_date, product_id, amount) VALUES
  ('2026-01-01', 1, 100),
  ('2026-01-01', 3,  30),
  ('2026-01-01', 2,  50),
  ('2026-02-01', 1, 200),
  ('2026-02-01', 3,  60),
  ('2026-02-01', 2,  75);