데이터베이스 질문과 답변
구분자를 사용해서 구분자의 속성값만큼 로우값을 늘리고 싶습니다
SELECT CONSULTING_ID
, REGEXP_REPLACE(REVERSE(REGEXP_REPLACE( REVERSE(TO_CHAR(RESULT_J_TYPE))
, '([0-9]{1})','\1,')), '^,','') as aaa
FROM WRKINNOV_CONSULT_RESULT
조회결과값입니다.
CS10000969 1,4
CS10000706
CS10000702 1,5
CS10002566 1,2,5
1,4
1.5
1.2.5 라고나오는 값을
CS10000969 1
CS10000969 4
CS10000702 1
CS10000702 5
CS10002566 1
CS10002566 2
CS10002566 5
결과값을 이리 만들고 싶습니다.
by 가을에사랑
[2018.05.10 16:50:41]
WITH t AS
(
SELECT 'CS10000969' AS CODE, '1,4' AS TYPE_ID_LIST FROM DUAL
UNION ALL SELECT 'CS10000706', '' FROM DUAL
UNION ALL SELECT 'CS10000702', '1,5' FROM DUAL
UNION ALL SELECT 'CS10002566', '1,2,5' FROM DUAL
)
SELECT
CODE,
REGEXP_SUBSTR(TYPE_ID_LIST,'[^,]+',1,LV) AS TYPE_ID
FROM T,
(SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 100) LE
WHERE 1=1
AND REGEXP_SUBSTR(TYPE_ID_LIST,'[^,]+',1,LV) IS NOT NULL
마농님께 배워서 사용했던걸 이용해서 만들어봤습니다.
by 우리집아찌
[2018.05.10 17:01:24]
-- 마농님께서 그렇게 가르치지 않으셨습니다. ㅎㅎ
WITH t AS
(
SELECT 'CS10000969' AS CODE, '1,4' AS TYPE_ID_LIST FROM DUAL
UNION ALL SELECT 'CS10000706', '' FROM DUAL
UNION ALL SELECT 'CS10000702', '1,5' FROM DUAL
UNION ALL SELECT 'CS10002566', '1,2,5' FROM DUAL
)
SELECT CODE
, REGEXP_SUBSTR(TYPE_ID_LIST,'[^,]+',1,LV) AS TYPE_ID
FROM T,
(SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= ( SELECT MAX(REGEXP_COUNT(TYPE_ID_LIST,',')) FROM T)) LE
WHERE LE.LV <= REGEXP_COUNT(TYPE_ID_LIST,',') + 1
ORDER BY CODE, TYPE_ID
by 우리집아찌
[2018.05.10 16:52:08]
DB는 무엇인가요?
by 마농
[2018.05.10 16:57:43]
regexp_replace 와 reverse 를 이용한 복잡한 작업은 불필요한 작업입니다.
WITH wrkinnov_consult_result AS
(
SELECT 'CS10000969' consulting_id, 14 result_j_type FROM dual
UNION ALL SELECT 'CS10000706', null FROM dual
UNION ALL SELECT 'CS10000702', 15 FROM dual
UNION ALL SELECT 'CS10002566', 125 FROM dual
)
SELECT consulting_id
, result_j_type
, lv
, SUBSTR(result_j_type, lv, 1) aaa
FROM wrkinnov_consult_result a
, (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 9) c
WHERE lv <= LENGTH(result_j_type)
ORDER BY consulting_id, lv
;
by 가을에사랑
[2018.05.10 16:59:32]
하나 더 배워갑니다. ^^//
by 햇살좋은날
[2018.05.15 17:20:22]
잘 배워갑니다 마농님! 늘 감사합니다 ^^
by 반댕이
[2018.05.10 17:00:06]
가을에사랑님 ,마뇽님 감사 합니다.
) 버튼을 클릭하여 작성 하시면 됩니다.