[SQL] MariaDB/Mysql 구분자(콤마, 파이프라인 등등)를 기준으로 열(row) 변환

MaraiDB 또는 Mysql에서 구분자로 넘어온 값을 다시 열로 변환(테이블)하는 방법을 해보려고 합니다. 쿼리를 작성할 때 100개 미만의 데이터 셋은 where절에 IN(..)절을 이용해서 전달하면 되지만, 1,000개, 10,000개 많은 양의 데이터를 조작하고자 할 때는 불편하게 됩니다. 또는 XML, CSV 파일의 행을 열로 바꾸고 행의 데이터를 열로 보고자 할 때 유용합니다.

1. 구분자 자르기를 이용해서 열로 변경하는 쿼리

/* 1. 사용자 변수 */
SET @IN_DELIMITER = '|';
SET @IN_DATA = '10|20|30||40|50||70|80|||90';

/* 구분자를 기준으로 로우(ROW) 데이터 생성 쿼리*/
SELECT    TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(A.DATA, @IN_DELIMITER, B.RN), @IN_DELIMITER, -1)) AS ROW_DATA
FROM      (SELECT    CASE
                       WHEN CONVERT((LENGTH(A.DATA) - LENGTH(REPLACE(A.DATA, @IN_DELIMITER, ''))) / LENGTH(','), UNSIGNED) > 0 THEN 
                         CONVERT((LENGTH(A.DATA) - LENGTH(REPLACE(A.DATA, @IN_DELIMITER, ''))) / LENGTH(',') + 1, UNSIGNED)
                       ELSE 0
                     END
                       AS DELIMITER_CNT
                    ,A.DATA
           FROM      (SELECT    @IN_DATA AS DATA) A) A /* [조건] 데이터 입력 */
          INNER JOIN (SELECT    CAST(seq AS INT) as RN FROM seq_1_to_1000) B ON (B.RN <= A.DELIMITER_CNT)
;

2. 데이터 결과

3. 열(Row) -> 행(Column) 변경(구분자)

SELECT   GROUP_CONCAT(A.SEQ) AS DATA
FROM     seq_1_to_10 A
;