Data formatting in postgresql

Question

Each "user_id" has multiple observations with distinct “Status” values.

user_id        status         score      region
  1001         applied         700    california
  1001         pre_approved    700    california
  1001         Approved        700    california

What i am trying to do is having one observation per user_id with flags indicating which "Status" values were observed for the "user_id".

user_id    applied_ind  pre_approved_ind   Approved_ind         score      region
  1001           1                 1               1             700    california

Gordon Linoff · Accepted Answer · 2020-02-19 22:22:41Z

2

You can use conditional aggregation:

select user_id,
       max(1) filter (where status = 'applied') as applied_ind,
       max(1) filter (where status = 'pre_approved') as pre_approved_ind,
       max(1) filter (where status = 'approved') as approved_ind,
       score, region
from t
group by user_id, score, region;

answered Feb 19, 2020 at 22:22

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Data formatting in postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related