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:

• CONTACTPERSON is CLIENT. If CONTACTCODE in (X,Y,Z) then list all distinct CONTACTCODE ( Ex: X,Y,...) else 0 ( MAIN column)
• CONTACTPERSON <> CLIENT. If CONTACTCODE in (X,Y,Z) then list all distinct CONTACTCODE ( Ex: X,Y,...) else 0 ( REF column) **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.

Continue reading...