By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1451502 fiddles created (18767 in the last week).
CREATE TABLE ListAggTest AS (
SELECT rownum Num1, DECODE(rownum,1,'2',to_char(rownum)) Num2 FROM dual
CONNECT BY rownum<=6
);
6 rows affected
hidden batch(es)
SELECT Num1, listagg(Num2,'-') WITHIN GROUP (ORDER BY NULL) OVER () Num2s
FROM ListAggTest;
NUM1
NUM2S
1
2-2-3-4-5-6
2
2-2-3-4-5-6
3
2-2-3-4-5-6
4
2-2-3-4-5-6
5
2-2-3-4-5-6
6
2-2-3-4-5-6
…
hidden batch(es)
SELECT Num1,
RTRIM(
REGEXP_REPLACE(
(listagg(Num2,'-') WITHIN GROUP (ORDER BY Num2) OVER ()),
'([^-]*)(-\1)+($|-)',
'\1\3'),
'-') Num2s
FROM ListAggTest;
