with q as (
 select 'card' n,   0 id from dual union all
 select 'pan' n,    0 id from dual union all
 select 'card' n,   0 id from dual union all
 select 'pan' n,    0 id from dual union all
 select 'card' n,   1 id from dual union all
 select 'pan' n,    1 id from dual union all
 select 'Stax' n,   1 id from dual union all
 select 'card' n,   2 id from dual union all
 select 'elimpion', 2 id from dual)

select id, prm1.val 
from 
  (select id, ','||listagg(n,',') WITHIN GROUP (order by n)||',' as agg from q group by id)
, (select 'card' as val from dual) prm1
, (select 'pan' as val from dual) prm2
where agg like '%,'||prm1.val||',%' and agg like '%,'||prm2.val||',%'