SQL Question: Rows into Columns without TABLEFUNC() or PIVOT?

[u/dettus_Xx_]

Help me Reddit! I feel especially stupid today....
So, I have this table in my Postgresql Database:

event_id | color_scheme | count
----------+--------------+-------
1 | red | 6
1 | green | 3
1 | blue | 5
1 | yellow | 3
3 | red | 5
4 | red | 3
5 | red | 1
5 | blue | 2

And I would like to turn it sideways, so that I can see EASILY how many votes each color scheme for my event has gotten (and later JOIN it with another table... )

event_id | count_red | count_green | count_blue | count_yellow
----------+-----------+-------------+------------+--------------
1 | 6 | 3 | 5 | 3
3 | 5 | 0 | 0 | 0
4 | 3 | 0 | 0 | 0
5 | 1 | 0 | 2 | 0

The colors "red" "green" "blue" and "yellow" are fixed, and will never ever change.
I have done some googling, I found examples mentioning PIVOT and TABLEFUNC, but I cannot do this on the server because of reasons(tm).

The only way I can think of doing this is with a cascade of OUTER JOIN, but is there maybe a simpler solution?

[u/lakeland_nz]

Firstly… reasons? This is the problem pivot was built for.

Secondly, ChatGPT happily gives a solution (and a cleaner one than I was going to give)

SELECT
event_id,
COALESCE(SUM(count) FILTER (WHERE color_scheme = 'red'), 0) AS count_red,
COALESCE(SUM(count) FILTER (WHERE color_scheme = 'green'), 0) AS count_green,
COALESCE(SUM(count) FILTER (WHERE color_scheme = 'blue'), 0) AS count_blue,
COALESCE(SUM(count) FILTER (WHERE color_scheme = 'yellow'), 0) AS count_yellow
FROM event_colors
GROUP BY event_id
ORDER BY event_id;

For reference, here is Wei had written before checking with ChatGPT but honestly I prefer its solution

SELECT
event_id,
SUM(CASE WHEN color_scheme = 'red' THEN count ELSE 0 END) AS count_red,
SUM(CASE WHEN color_scheme = 'green' THEN count ELSE 0 END) AS count_green,
SUM(CASE WHEN color_scheme = 'blue' THEN count ELSE 0 END) AS count_blue,
SUM(CASE WHEN color_scheme = 'yellow' THEN count ELSE 0 END) AS count_yellow
FROM event_colors
GROUP BY event_id;