CODEWITHSUNDEEP
sqlpostgresqlpivot
imnotyourguru
imnotyourguru

Reputation: 65

Split numeric column into multiple columns

I have SQL-query which grouped owners and their statuses from two tables:

select value, count(distinct owner_id) as owners, account.status
from accounts_login_history
left join accounts on accounts.id = accounts_login_history.owner_id
where date >= '2020-02-01'
and owner_type = 1
group by value, accounts.status

Output:

enter image description here

How I should change my query for split status column into all categorical values (status have 5 unique values)?

enter image description here

I use postgresql.

Thank you!

Upvotes: 1

Views: 301

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270683

You can use conditional aggregation, which in Postgres is best accomplished using filter:

select value, count(distinct owner_id) as owners,
       max(a.status) filter (where status = 1) as status_1,
       max(a.status) filter (where status = 2) as status_2,
       max(a.status) filter (where status = 3) as status_3,
       max(a.status) filter (where status = 4) as status_4,
       max(a.status) filter (where status = 5) as status_5
from accounts_login_history alh left join
     accounts a 
     on accounts.id = alh.owner_id
where date >= '2020-02-01' and
      owner_type = 1
group by value;

This returns nulls for the missing values rather than 0. You can -- of course -- use coalesce() to change this to 0. However, I prefer nulls.

Upvotes: 2

Related Questions