콤마(,) 로 이루어진(LISTAGG) 문자열 ROW로 변환하기(oracle, mariadb)

LISTAGG과 같은 방식으로 여러 ROW를 하나의 ROW를 합치는 경우가 있다.

그것의 반대의 경우에 사용하는 쿼리다.

 

1 . 먼저 오라클의 경우 다음과 같다. (REGEXP_SUBSTR 인수에 최대 4개 입력가능한 Mysql 버전이라면 Mysql도 동일할 듯)

B부분은 몇개까지 ROW로 풀어낼 것인지를 나타내는 임시 테이블이다.

WITH TMP(NO, YEAR, CODE) AS (
    SELECT '2022027290', '2022', '2022000011,2022000022, 2022000023' FROM DUAL   
)
SELECT A.NO
     , A.YEAR
     , REGEXP_SUBSTR(REPLACE(A.CODE, ' ', ''),'[^,]+', 1, B.LV) AS CODE
  FROM TMP A 
     , (SELECT LEVEL AS LV 
          FROM DUAL 
       CONNECT BY LEVEL <= 10) B -- 최대 개수 10개
 WHERE REGEXP_SUBSTR(REPLACE(A.CODE, ' ', ''), '[^,]+', 1, B.LV) IS NOT NULL
 ORDER BY A.NO, A.YEAR, A.CODE

 

'[^,]+' << 이 부분에서 , 기준으로 ROW를 나눈다

 

2. MariaDB

MariaDB는 REGEXP_SUBSTR가 자르기 밖에 못한다. 

SELECT REGEXP_SUBSTR('AS,D', '[^,]+'); -- 결과 : 'AS'

위 처럼 한번 자르기 뿐이 안된다. 따라서 다음과 같이 조금 귀찮게 변경해줬다.

WITH TMP(NO, YEAR, CODE) AS (
    SELECT '2022027290', '2022', '2022000011,2022000022, 2022000023' FROM DUAL   
)
SELECT A.NO
     , A.YEAR
--      , b.lv
--      , CHAR_LENGTH(REPLACE(A.CODE, ' ', '')) - CHAR_LENGTH(REPLACE(REPLACE(A.CODE, ' ', ''),',',''))
--      , SUBSTRING_INDEX(REPLACE(A.CODE, ' ', ''), ',', B.LV)
     , SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(A.CODE, ' ', ''), ',', B.LV), ',', -1)
  FROM TMP A 
     , (SELECT 1 AS LV
         UNION ALL
        SELECT 2
         UNION ALL
        SELECT 3
         UNION ALL
        SELECT 4
         UNION ALL
        SELECT 5
         UNION ALL
        SELECT 6
        ) B 
 WHERE SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(A.CODE, ' ', ''), ',', B.LV), ',', -1) IS NOT NULL
   AND CHAR_LENGTH(REPLACE(A.CODE, ' ', '')) - CHAR_LENGTH(REPLACE(REPLACE(A.CODE, ' ', ''),',','')) >= B.LV
 ORDER BY A.NO, A.YEAR, A.CODE