Case statements around values in a list

Question

I have a table that looks like below after performing certain joins:

create table test(id varchar(10), sub varchar(20));

insert into test values(1,'a')
insert into test values(1,'b')
insert into test values(2,'a')
insert into test values(3,'a')
insert into test values(3,'c')

I want see what values a particular id has in sub column and output as following (expected results):

1,'both a and b'
2,'only a'
3,'both a and c'

I tried the following:

select id, case when sub in ('a') then 'only a'
when sub in ('a','b') then 'both a and b')
else 'only b'
end as subs from test group by 1,2;

Any help will be appreciated.

Answer 1

You can do aggregation :

select id, (case when max(sub) = min(sub) and min(sub) = 'a' 
                 then 'only a'
                 when max(sub) = min(sub) and min(sub) = 'b'  
                 then 'only b'
                 when max(sub) <> min(sub)
                 then 'both a and b' 
            end)
from test t
where t.sub in ('a', 'b')
group by id;

EDIT : Removed where clause & do concat.

select id, (case when max(sub) = min(sub) 
                 then 'only' || min(sub)
                 when max(sub) <> min(sub)
                 then 'multiple'
            end)
from test t
group by id;