WITH t AS
(
SELECT 'A' cd, '1' v FROM dual
UNION ALL SELECT 'B', '2,3'   FROM dual
UNION ALL SELECT 'C', '2,5,6' FROM dual
UNION ALL SELECT 'D', '1,6'   FROM dual
)
SELECT LISTAGG(x, ',') WITHIN GROUP(ORDER BY x) x
  FROM (SELECT DISTINCT x
          FROM t
             , XMLTABLE(v COLUMNS x NUMBER PATH '.')
        )
;
-- http://gurubee.net/article/55635

인공지능 ChatGPT에 질문했더니 아래와 같은 쿼리를 만들어주네요ㅎㅎ

Q :

오라클에 T라는 테이블은 아래와 같이 구성되어있어.

WITH t AS
(
SELECT 'A' cd, '1' v FROM dual
UNION ALL SELECT 'B', '2,3'   FROM dual
UNION ALL SELECT 'C', '2,5,6' FROM dual
UNION ALL SELECT 'D', '1,6'   FROM dual
)

컬럼 v에는 콤마로 구분되는 숫자가 있는데
모든 행의 v의 숫자를 가져와 중복을 제거하고 다음과 같은 결과를 도출하는 쿼리를 만들어 줘.
1,2,3,5,6

A :

WITH t AS (
SELECT 'A' cd, '1' v FROM dual
UNION ALL SELECT 'B', '2,3' FROM dual
UNION ALL SELECT 'C', '2,5,6' FROM dual
UNION ALL SELECT 'D', '1,6' FROM dual
),
t_split AS (
SELECT cd, TRIM(REGEXP_SUBSTR(v, '[^,]+', 1, LEVEL)) AS num
FROM t
CONNECT BY LEVEL <= LENGTH(v) - LENGTH(REPLACE(v, ',', '')) + 1
)
SELECT listagg(num, ',') WITHIN GROUP (ORDER BY num) AS result
FROM (SELECT DISTINCT num FROM t_split)

다른 방법도 알려달랬더니 아래 쿼리도 알려주는데

테스트 환경이 안 되어서 잘 돌아가는지는 모르겠네요.

SELECT column_value as result
FROM xmltable(
'for $i in distinct-values(/ROWSET/ROW/v)
return $i'
PASSING XMLType(
'<ROWSET>' ||
(
SELECT replace(v,',') as v
FROM t
FOR XML PATH(''), type
)
|| '</ROWSET>'
)
)

AI 발전 속도가 참 대단합니다ㅎㄷㄷ