Oracle LISTAGG – How to De-Duplicate from LISTAGG

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

You need to pick one of the string aggregation techniques that works with your version and supports ordering. For example, I've adapted the "ROW_NUMBER() and SYS_CONNECT_BY_PATH functions in Oracle 9i" technique from the excellent ORACLE-BASE website:

WITH a as ( SELECT t3_field1,
                   t3_field2,
                   LTRIM(MAX(SYS_CONNECT_BY_PATH(field3,', '))
                       KEEP (DENSE_RANK LAST ORDER BY curr),', ') AS agg
            FROM   (SELECT field1 t3_field1,
                           field2 t3_field2,
                           field3,
                           ROW_NUMBER() 
                             OVER (PARTITION BY field1, field2 ORDER BY field3)
                             AS curr,
                           ROW_NUMBER() 
                             OVER (PARTITION BY field1, field2 ORDER BY field3) -1 
                             AS prev
                    FROM   Table3)
            GROUP BY t3_field1, t3_field2
            CONNECT BY prev = PRIOR curr 
                 AND t3_field1 = PRIOR t3_field1 
                 AND t3_field2 = PRIOR t3_field2
            START WITH curr = 1 ),
     s as ( SELECT t3.field1 t3_field1,
                   t3.field2 t3_field2,
                   SUM(t2.field1) sum_t2_field1,
                   SUM(t2.field2) sum_t2_field2
            FROM Table1 t1, Table2 t2, Table3 t3
            WHERE t1.id = t2.idoft1 AND t2.id = t3.idoft2 AND t1.id = 3
            GROUP BY t3.field1, t3.field2 )
SELECT * FROM a JOIN s USING (t3_field1,t3_field2);

Results:

| T3_FIELD1 | T3_FIELD2 |                                             AGG | SUM_T2_FIELD1 | SUM_T2_FIELD2 |
|-----------|-----------|-------------------------------------------------|---------------|---------------|
|      asdf |      test | and_one_more_text, some_another_text, text_here |          1998 |           210 |
|     asdf2 |      test |                           text_in_another_group |           666 |            70 |

SQLFiddle here