CODEWITHSUNDEEP
sqloracle-database
Thuong Duyen Truong
Thuong Duyen Truong

Reputation: 1

Oracle LISTAGG DISTINCT with multiple conditions

For Oracle12, I have the table shown below

ID CONTACTCODE CONTACTPERSON
1 X CLIENT
1 X CLIENT
1 X WIFE
1 Y CLIENT
1 Z WIFE
1 Z CLIENT
1 Z HUSBAND
1 W CLIENT
2 Y CHILD
2 Y WIFE
2 Y CLIENT
3 Y HUSBAND
3 W CLIENT

And I want the result like:

Expected table:

ID MAIN REF
1 X,Y, Z X,Z
2 Y Y
3 0 Y

I used row_number partition by ID, CONTACTCODE and then LISTAGG where row_number = 1 but I had many problems.

Upvotes: 0

Views: 464

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270993

You can use row_number() and conditional aggregation:

select id,
       (case when sum(case when contactperson = 'Client' then 1 else 0 end) > 0
             then listagg(case when seqnum = 1 and contactcode in ('X', 'Y', 'Z')
                               then contactcode
                          end, '') within group (order by contactcode)
        end),
       listagg(case when seqnum = 1 and contactcode in ('X', 'Y', 'Z')
                    then contactcode
               end, '') within group (order by contactcode)
from (select t.*,
             row_number() over (partition by id, contactcode order by id) as seqnum
      from t
     ) t
group by id

Upvotes: 0

Popeye
Popeye

Reputation: 35930

You can use the distinct, case..when and LISTAGG as follows:

select id, 
       coalesce(listagg(case when isclient = 'CLIENT' then contactcode end,',') 
                  within group (order by contactcode), '0') as maincode,
       coalesce(listagg(case when isclient = 'NOT CLIENT' then contactcode end,',') 
                  within group (order by contactcode), '0') as ref 
(select distinct id, contactcode, 
        case when contactperson = 'CLIENT' then 'CLIENT' else 'NOT CLIENT' end as isclient
  from your_table t
 where t.contactcode in ('X', 'Y', 'Z') ) t
 group by id

Upvotes: 2

Related Questions