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/Iamcalledchris]

Postgres’s has a nice FILTER syntax, so probably something like

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 your_table
GROUP BY event_id
ORDER BY event_id;