Multiple categories on one line

Trying to identify individuals in 2 groups, showing the id of the person, and the two group names on one line.

Tried case statements and joins but they are showing up on multiple lines. So that one ID shows up twice, once for one group and once for another.

select distinct
par.pid

from
participants par

where
par.participant_code = 'MEMBER'
and par.retirement in ('P', 'O')
and par.retirement_code in ('PS','AS', 'CP', 'EP')
group by par.pid
having count (*) > 1

The above code gets me all the individuals I need, but when I try to show the names of the retirement code to which they belong I get 2 lines per ID.

tried this and got the duplicate ID rows:

Select distinct
AZ.pid,
 case when OT.retirement = 'P' then OT.retirement_code end Ar_Sys,
  case when OT.retirement = 'O' then OT.retirement_code end Ot_Sys

from
(select
par.pid

from
participants par

where
par.participant_code = 'MEMBER'
and par.retirement in ('P', 'O')
and par.retirement_code in ('PS','AS', 'CP', 'EP')
group by par.pid
having count (*) > 1) AZ,

(select
pa.pid,
pa.retirement,
pa.retirement_code

from
participants pa

where
pa.participant_code = 'MEMBER'
and pa.retirement in ('P', 'O')
and pa.retirement_code in ('PS','AS', 'CP', 'EP'))OT

where 
AZ.pid=OT.pid

Answer

You can use conditional aggregation :

select par.pid,
       max(case when retirement = 'P' then retirement_code end) Ar_Sys,
       max(case when retirement = 'O' then retirement_code end) Ot_Sys    
  from participants par    
 where par.participant_code = 'MEMBER'
   and par.retirement in ('P', 'O')
   and par.retirement_code in ('PS','AS', 'CP', 'EP')
 group by par.pid
having count (*) > 1

Advertisement

Answer