Pivot a known set of categories ​

For a known set of categories, calculate one conditional aggregate per output column. This makes both the category list and the handling of missing categories explicit.

CREATE TABLE monthly_sales(account_id INTEGER, category TEXT, amount INTEGER);
INSERT INTO monthly_sales VALUES
    (1, 'books', 30), (1, 'tools', 20), (2, 'books', 15);

SELECT account_id,
       sum(CASE WHEN category = 'books' THEN amount ELSE 0 END) AS books,
       sum(CASE WHEN category = 'tools' THEN amount ELSE 0 END) AS tools
FROM monthly_sales
GROUP BY account_id
ORDER BY account_id
FORMAT csv;

The ELSE 0 supplies zero for a category absent from an existing account group. It does not create accounts absent from the source altogether. Join against a separate account table when such accounts must also appear.

Changing the output column set requires changing the SQL. For a large or changing category list, return rows grouped by account and category, then arrange them for display in the consuming application. Avoid silently turning unknown categories into an existing column. Check category spellings and NULL values before comparing pivot totals with an ungrouped total.

See GROUP BY and filtered aggregates.