Eliminate duplicates in ListAgg (Oracle) Prior to Oracle 11.2 I was using a custom aggregate function to concatenate a column into a row.  11.2 Added the LISTAGG function, so I am trying to use that instead.  My problem is that I need to eliminate duplicates in the results and don't seem to be able to do that.

Here is an example.

CREATE TABLE ListAggTest AS (
  SELECT rownum Num1, DECODE(rownum,1,'2',to_char(rownum)) Num2 FROM dual 
     CONNECT BY rownum&lt;=6
  );
SELECT * FROM ListAggTest;


      NUM1 NUM2
---------- ---------------------
         1 2
         2 2                    &lt;&lt; Duplicate 2
         3 3
         4 4
         5 5
         6 6


What I want to see is this:

      NUM1 NUM2S
---------- --------------------
         1 2-3-4-5-6
         2 2-3-4-5-6
         3 2-3-4-5-6
         4 2-3-4-5-6
         5 2-3-4-5-6
         6 2-3-4-5-6


Here is a listagg version that is close, but doesn't eliminate duplicates.

SELECT Num1, listagg(Num2,'-') WITHIN GROUP (ORDER BY NULL) OVER () Num2s 
FROM ListAggTest;


I have a solution, but it's worse than continuing to use the custom aggregate function.
You can use regular expressions and regexp_replace to remove the duplicates after concatenation with listagg:

SELECT Num1, 
       RTRIM(
         REGEXP_REPLACE(
           (listagg(Num2,'-') WITHIN GROUP (ORDER BY Num2) OVER ()), 
           '([^-]*)(-\1)+($|-)', 
           '\1\3'),
         '-') Num2s 
FROM ListAggTest;


This could be tidier if Oracle's regex flavour supported lookahead or non-capturing groups, but it doesn't.  

However this solution does avoid scanning the source more than once.

DBFiddle here