SQL & PL/SQL

Hi

I have written a query to list the parent tables in a single column format using listagg - However one child table references many columns in a parent table and the tables is repeated N times. I want to get the distinct list of parent tables and I can't get it working. Please find my query below.


SELECT a.table_name,listagg(c_pk.table_name,';') within group (order by a.table_name ) "r_table_count"
FROM all_cons_columns a
JOIN all_constraints c ON a.owner = c.owner
AND a.constraint_name = c.constraint_name
JOIN all_constraints c_pk ON c.r_owner = c_pk.owner
AND c.r_constraint_name = c_pk.constraint_name
WHERE c.constraint_type = 'R'
AND a.table_name = '&TableName'
group by a.table_name
order by 1,2

The output from this query is Child parent1:parent2:parent2:parent3

I need the output as Child parent1:parent2:parent3

Thanks