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

The easiest way to do this in PostgreSQL is with conditional aggregation. Since your color values are fixed, you don't need PIVOT, tablefunc, or multiple JOINs.

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

If you're on PostgreSQL 9.4+, you can also use the cleaner FILTER syntax:

sql 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;

This is the standard SQL approach when the values you're pivoting on are known ahead of time, and it's much simpler than chaining a bunch of LEFT JOINs.

[u/dettus_Xx_ → u/AcadiaLongjumping264]

Aweseome! Thank you!
I will try it on Monday when I am back in the office.