By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select * from V$VERSION;
| BANNER | BANNER_FULL | BANNER_LEGACY | CON_ID |
|---|---|---|---|
| Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production | Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production Version 18.4.0.0.0 |
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production | 0 |
with test( str) as
( select 'M013,M007,M019,YYY,M018,XXX,999' from dual)
select listagg(estr,',') within group (order by estr)
from (select 'x' x, regexp_substr(str,'[^,]+', 1, level) estr
from test connect by regexp_substr(str, '[^,]+', 1, level) is not null
)
group by x;
| LISTAGG(ESTR,',')WITHINGROUP(ORDERBYESTR) |
|---|
| 999,M007,M013,M018,M019,XXX,YYY |