한 Row에 들어 있는 값을 여러 Row로 쿼리문 만들기
1) 한 Row의 구분자가 ','로 되어 있는 경우
2) ','개수 Row + 1개의 Row의 갯수 쿼리문 만들기
CREATE TABLE tb_list(idx integer not null primary key,
code_id varchar2(10),
code varchar2(100));
insert into tb_list(idx, code_id, code) values(1, '1012111300', '3,2,3,4,1,1,1,2,3,4,,4,2,3,1,2,4,1,2,1,3,4,3,2,2,3,2,3,4,1,1,1,2,3,4,,4,2,3,1');
CREATE TABLE tb_cnt(cnt_no INTEGER NOT NULL PRIMARY key);
INSERT INTO tb_cnt(cnt_no)
values(1) ,(2) ,(3) ,(4) ,(5) ,(6) ,(7) ,(8) ,(9) ,(10) ,(11) ,(12) ,(13) ,(14) ,(15)
,(16) ,(17) ,(18) ,(19) ,(20) ,(21) ,(22) ,(23) ,(24) ,(25) ,(26) ,(27) ,(28) ,(29) ,(30)
,(31) ,(32) ,(33) ,(34) ,(35) ,(36) ,(37) ,(38) ,(39) ,(40)
;
4) 쿼리문
SELECT a.idx, a.code_id
, REPLACE(substring_index(a.code,',',b.cnt_no), SUBSTRING_INDEX(a.code,',',b.cnt_no-1)||',','') AS answer
FROM (
SELECT idx
, code_id
, code
FROM t_list ) a, tb_cnt b
WHERE code_id = '1012111300'
AND b.cnt_no <= LENGTH(a.code) - LENGTH(REPLACE(a.code, ',','')) +1
SELECT
code,
LEVEL,
REPLACE(REPLACE(SUBSTRING_INDEX(code,',',LEVEL), SUBSTRING_INDEX(code,',',LEVEL -1),''),',','') code_split
FROM (SELECT '3,2,3,4,1,1,1,2,3,4,,4,2,3,1,2,4,1,2,1,3,4,3,2,2,3,2,3,4,1,1,1,2,3,4,,4,2,3,1' AS code) Z
CONNECT BY LEVEL <= LENGTH(code) - LENGTH(REPLACE(code,',','')) - 1;