How to flag if something doesn't appear in a list

Question

I am not sure how to word this exactly in a title, but I have a list of people who have multiple records, each row is a record and one field lists which type of record it is. What I need to do is to be able to flag if somebody doesn't have a certain type of record.

Data is as follows

person id       type
1                a
1                b
1                c
2                b
3                a
3                e
3                d
3                c
4                a
4                c
4                e

In this list person id 3 and 4 do not have type b, so I would like to create a flag to highlight that. In the output data the type is not required, so it will just be Person ID and the flag.

Any help appreciated.

Answer 1

Use window function :

select t.*, 
       coalesce(max(case when type = 'b' then 'yes' end) over (partition by personid), 'no') as b_flag
from table t;

You can also do aggregation :

select personid, 
       coalesce(max(case when type = 'b' then 'yes' end), 'no') as b_flag
from table t
group by personid;