-- Created on savesnippets.com · https://savesnippets.com/yNDaqrytKVffaC
-- Source: monthly sales rows
--   month   | category | revenue
--   2025-01 | widgets  | 100
--   2025-01 | gizmos   | 200
--   2025-02 | widgets  | 150
--   ...

-- Pivot — one column per category
SELECT month,
       SUM(CASE WHEN category = 'widgets' THEN revenue END) AS widgets,
       SUM(CASE WHEN category = 'gizmos'  THEN revenue END) AS gizmos,
       SUM(CASE WHEN category = 'gadgets' THEN revenue END) AS gadgets,
       SUM(revenue)                                          AS total
FROM   sales
GROUP  BY month
ORDER  BY month;

-- Postgres FILTER syntax — same idea, cleaner
SELECT month,
       SUM(revenue) FILTER (WHERE category = 'widgets') AS widgets,
       SUM(revenue) FILTER (WHERE category = 'gizmos')  AS gizmos,
       SUM(revenue)                                      AS total
FROM   sales
GROUP  BY month;

-- For dynamic pivots (unknown column set at query time), reach for
-- the crosstab() function (PostgreSQL tablefunc extension) or build the
-- query string in your application.