using LISTAGG for so many columns without repeating (DISTINCT)

Viewed 10

in my Oracle 12c database I have 3 tables, my query is like follows :

SELECT t1.name AS col1,
       LISTAGG(DISTINCT t2.name, ',') WITHIN GROUP (ORDER BY t2.name) AS col2,
       LISTAGG(DISTINCT t3.name, ',') WITHIN GROUP (ORDER BY t3.name) AS col3
  FROM table1 t1
  LEFT JOIN table2 t2
    ON t2.id_t1 = t1.id
  LEFT JOIN table3 t3
    ON t3.id_t1 = t1.id

the result would be something like this :

col1    col2    col3    
1        a,b     #,#

how can I display # only once regarding col3 results. or in other words how can I write this in 12C :

SELECT LISTAGG(DISTINCT the_column, ',') WITHIN GROUP (ORDER BY the_column)
  FROM the_table
0 Answers
Related