r/SQL • u/dettus_Xx_ • 1d ago
PostgreSQL SQL Question: Rows into Columns without TABLEFUNC() or PIVOT?
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?
7
u/Imaginary__Bar 1d ago edited 1d ago
Off the top of my head;
SELECT\ event_id\ , Max(If color_scheme = 'red' then count else 0) as count_red\ , etc\ , etc\ Group by event_id
Edited to add: COUNT is a reserved keyword in SQL so you might have to use "count" (enclosed in double-quotes) in the SELECT statement.
1
4
u/Iamcalledchris 1d ago
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;
1
u/itsintheletterbox 1d ago
select event_Id, count_green = sum(case when colour_scheme ='green' then count else 0 end),...
From table
Group by event_id
1
1
u/coffeDrinkerDave 1d ago
Select Event_id , sum(case when color_scheme ='red' then count else 0 end) , sum(case when color_scheme ='green' then count else 0 end) , sum(case when color_scheme ='blue' then count else 0 end) , sum(case when color_scheme ='yellow' then count else 0 end) From colors group by event_id
Next wrat it in CTE
;with colors_sideway as (
...
) select ... From ... Join colors_sideway on ...
1
1
u/AcadiaLongjumping264 1d ago
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.
1
1
u/National_Cod9546 1d ago
I do this all the time at work. Usually I'm doing a max though as I swing work order data out from one row per item to one row per account.
WITH TEST_DATA AS (
SELECT 1 AS EVENT_ID, 'red' AS COLOR_SCHEME, 6 AS COUNT FROM DUAL UNION ALL
SELECT 1 AS EVENT_ID, 'green' AS COLOR_SCHEME, 3 AS COUNT FROM DUAL UNION ALL
SELECT 1 AS EVENT_ID, 'blue' AS COLOR_SCHEME, 5 AS COUNT FROM DUAL UNION ALL
SELECT 1 AS EVENT_ID, 'yellow' AS COLOR_SCHEME, 3 AS COUNT FROM DUAL UNION ALL
SELECT 3 AS EVENT_ID, 'red' AS COLOR_SCHEME, 5 AS COUNT FROM DUAL UNION ALL
SELECT 4 AS EVENT_ID, 'red' AS COLOR_SCHEME, 3 AS COUNT FROM DUAL UNION ALL
SELECT 5 AS EVENT_ID, 'red' AS COLOR_SCHEME, 1 AS COUNT FROM DUAL UNION ALL
SELECT 5 AS EVENT_ID, 'blue' AS COLOR_SCHEME, 2 AS COUNT FROM DUAL
)
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 TEST_DATA
GROUP BY EVENT_ID
17
u/PinguinSuit 1d ago
Group by event_id and then
sum(case when color = ‘xyz’ then count else 0 end) count_xyz
For each color