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<=6
  );
SELECT * FROM ListAggTest;

NUM1 NUM2
---------- ---------------------
1 2
2 2

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

StackExchange Database Administrators Q#696, answer score: 38