Search code examples
sqlpostgresqlcrosstab

PostgreSQL, aggregate and combine

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?

Solution

  • 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