PostgreSQL, aggregate and combine-CodePudding

I have a table in PostgreSQL:

name TEXT,
country_code TEXT
visited TIMESTAMP

I know country_code can only be US, UK or CH.

I would like an output similar to:

name,  US-last-visited, UK-last-visited, CH-last-visited
Gregg  null             2022-01-02       2001-01-02
Paul   1999-01-10       null             2021-01-03

Can that be done somewhat simple with e.g. GROUP BY and ARRAY_AGG or similar?

CodePudding user response：

You can use filtered aggregation:

select name, 
       max(visited) filter (where country_code = 'UK') as uk_last_visited,
       max(visited) filter (where country_code = 'US') as us_last_visited,
       max(visited) filter (where country_code = 'CH') as ch_last_visited
from the_table
group by name

CodePudding user response：

You can use conditional aggregation:

select name
     , max(case when country_code = 'US' then visited end) as US_last_visited
     , max(case when country_code = 'UK' then visited end) as UK_last_visited
     , max(case when country_code = 'CH' then visited end) as CH_last_visited
from t
group by name