I have result from my query like this on PostgreSQL:

and i need to group team's in new column like team_type and sum values regard it.

So my prefered output should be like:

my sql script:

with res as (
select
    date,
    depart,
    case
        when team_name = 'blue' then scores
    end blue_team,
    case
        when team_name = 'red' then scores
    end red_team,
    case
        when team_name = 'green' then scores
    end green_team
from results)
select
    date,
    depart,
    sum(blue_team) blue_team,
    sum(red_team) red_team,
    sum(green_team) green_team
from res
group by 1,2

CodePudding user response：

then simple union all would answer your question:

select date, depart, 'blue_team' as teamtype, blue_team as value from game_results
union all 
select date, depart, 'red_team' as teamtype, red_team as value from game_results
union all
select date, depart, 'green_team' as teamtype, green_team as value from game_results

or you can use unnest :

SELECT date, depart
, UNNEST(ARRAY['blue_team', 'red_team', 'green_team']) AS teamtype
, UNNEST(ARRAY[blue_team, red_team, green_team]) AS value
FROM game_results

fiddle