How to output results for each year in a new column?

Question

This code shows data in a one line, but I need results of each year in separat column. Could you help me?

SELECT 
EXTRACT (YEAR FROM s.time_id)::int,
p.prod_subcategory,
sum(s.amount_sold)
FROM sales s
JOIN products p ON p.prod_id = s.prod_id
WHERE EXTRACT (YEAR FROM s.time_id) IN (1998,1999,2001)
GROUP BY EXTRACT (YEAR FROM s.time_id)::int, p.prod_subcategory
ORDER BY date_part;

Answer 1

Use conditional aggregation:

SELECT p.prod_subcategory,
       SUM(s.amount_sold) FILTER (WHERE EXTRACT(YEAR FROM s.time_id) = 2000) as sales_2000,
       SUM(s.amount_sold) FILTER (WHERE EXTRACT(YEAR FROM s.time_id) = 1999) as sales_1999,
       SUM(s.amount_sold) FILTER (WHERE EXTRACT(YEAR FROM s.time_id) = 1998) as sales_1998
FROM sales s JOIN
     products p
     ON p.prod_id = s.prod_id
WHERE EXTRACT (YEAR FROM s.time_id) IN (1998,1999,2001)
GROUP BY p.prod_subcategory;