dbms는 무엇인가요? 오라클? mysql? mssq?

정렬이 보장되어야하나요?

-- 손으로 옮겨 적어서 오타 있을 수 있어요. 
WITH T AS ( 
select 'C01,C02,D01' as BANG from dual union all 
select 'C01,C02' as BANG from dual union all 
select 'A01,A03' as BANG from dual 
) 

-- 19C 
SELECT LISTAGG(DISTINCT REGEXP_SUBSTR(BANG,'[^,]+',1,LV ) , ',') WITHIN GROUP( ORDER BY REGEXP_SUBSTR(BANG,'[^,]+',1,LV ) ) 
  FROM T A 
     ,(SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= (SELECT MAX(REGEXP_COUNT(BANG,',')) FROM T ) + 1 ) B 
WHERE LV <= REGEXP_COUNT(A.BANG,',') + 1 

-- 19C 이전 버젼 
SELECT LISTAGG(V , ',') WITHIN GROUP( ORDER BY V ) 
  FROM (SELECT DISTINCT REGEXP_SUBSTR(BANG,'[^,]+',1,LV ) V 
          FROM T A 
             ,(SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= (SELECT MAX(REGEXP_COUNT(BANG,',')) FROM T ) + 1 ) B 
         WHERE LV <= REGEXP_COUNT(A.BANG,',') + 1

XMLTABLE 함수를 사용한 방법입니다.

WITH TMP AS ( 
    SELECT 'Z01,B01' AS BANG FROM DUAL UNION ALL
    SELECT 'C01,C02,D01' AS BANG FROM DUAL UNION ALL 
    SELECT 'C01,C02' AS BANG FROM DUAL UNION ALL 
    SELECT 'A01,A03' AS BANG FROM DUAL 
) 
SELECT LISTAGG(DISTINCT trim(column_value)) WITHIN GROUP(ORDER BY trim(column_value)) AS BANG_NM
FROM TMP, xmltable(('"' || REPLACE(BANG, ',', '","') || '"'));

XMLTABLE 함수를 사용하여 각 BANG 값을 컴마(,)로 분리하고, DISTINCT 키워드를 사용하여 중복된 값을 제거합니다. 그리고 ORDER BY 절을 사용하여 결과 집합을 정렬합니다.

[19C이하]

WITH TMP AS ( 
    SELECT 'Z01,B01' AS BANG FROM DUAL UNION ALL
    SELECT 'C01,C02,D01' AS BANG FROM DUAL UNION ALL 
    SELECT 'C01,C02' AS BANG FROM DUAL UNION ALL 
    SELECT 'A01,A03' AS BANG FROM DUAL 
) 
SELECT LISTAGG(BANG, ',') WITHIN GROUP(ORDER BY BANG) AS BANG_NM
FROM (
    SELECT DISTINCT TRIM(COLUMN_VALUE) AS BANG
    FROM TMP, XMLTABLE(('"' || REPLACE(BANG, ',', '","') || '"'))
);