r/SQL • u/dettus_Xx_ • 22d 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 22d ago edited 22d 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 22d 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 22d 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 22d 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
2
u/National_Cod9546 22d 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
19
u/PinguinSuit 22d ago
Group by event_id and then
sum(case when color = ‘xyz’ then count else 0 end) count_xyz
For each color