데이터베이스 질문과 답변
아래와 같이 답변을 주셨는데 저도 LV 변수 에러가 나서 저부분을 이렇게 저렇게 바꿔봤는데 원하는
결과가 아니라서 답변을 다시 올렸었습니다.
WITH temp AS
(
SELECT 'A1|A2|A3|A4 , B1|B2|B3|B4 , C1|C2|C3|C4' str FROM dual
UNION ALL SELECT 'F1|F2|F3|F4 , G1|G2|G3|G4' FROM dual
UNION ALL SELECT 'H1|H2|H3|H4' FROM dual
UNION ALL SELECT null FROM dual
)
SELECT rn, str
, WM_CONCAT(REGEXP_SUBSTR(str, '[^|]+', 1, 1)) v1
, WM_CONCAT(REGEXP_SUBSTR(str, '[^|]+', 1, 2)) v2
FROM (
SELECT rn, str
, REGEXP_SUBSTR(str, '[^, ]+', 1, lv) v
, lv
FROM (SELECT ROWNUM rn, str FROM temp)
, (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 99)
WHERE lv(+) <= length(str) - length(replace(str, ',', ''))
)
GROUP BY rn, str
위 답변 쿼리를 실행을 하면 아래와 같이 결과값이 나옵니다.
| RN | str | v1 | v2 |
| 1 | A1|A2|A3|A4 , B1|B2|B3|B4 , C1|C2|C3|C4 | A1,A1 | A2,A2 |
| 2 | F1|F2|F3|F4 , G1|G2|G3|G4 | F1 | F2 |
| 3 | H1|H2|H3|H4 | H1 | H2 |
| 4 |
1번에 V1 은 A1, B1,C1 V2컬럼은 A2, B2, C2 이고
2번에 V1 은 F1, G1 V2 컬럼은 F2, G2 가 나와야 합니다.
아래 제가 올렸던 질문원문을 다시 한번 올립니다. 꼭 답이 필요하다기 보단
저도 구루에서 답변을 받으면 이렇게 하는구나 응용하고 배우는중이라 질문을 다시 올려봅니다.
사용 디비는 오라클 입니다. 의미만이라도 잘 전해드리겠습니다.
'|' 으로 이어져 있는 4개(A|B|C|D) 의 값들이 콤마(,) 로 또 구분되어 이어져 있습니다.
콤마로 이어진 값들은 없을수도 있고 콤마로 이어진 갯수가 많을 수도 있습니다.
일단 4개의 행을 예로 들어 봤습니다.
with temp as(
select 'A1|A2|A3|A4 , B1|B2|B3|B4 , C1|C2|C3|C4' str from dual
union all select 'F1|F2|F3|F4 , G1|G2|G3|G4' from dual
union all select 'H1|H2|H3|H4' from dual
union all select null from dual
)
위와 같은 경우 제가 하고 싶은 작업은 콤마로 이어진 값들중에서 '|' 이어진 첫번째, 두번째를 더해서 컬럼으로 만들고 싶습니다.
temp 를 select 하면서 아래 방식으로 나오게 하고 싶습니다.
select (A1 || B1 || C1) as col1 , (A2 || B2 || C2) as col2
(F1 || G1) as col1, (F2 || G2) as col2
(H1) as col1 , (H2) as col2
null as col1 , null as col2
from temp <==temp 행이 4개 이므로 위 처럼 나오게 하고 싶습니다.
계산의 예를 보이기 위해서 4줄의 설명을 드렸습니다.
콤마로 이어진 컬럼에 데이타가 10개라면 (A1 || B1 || C1 ....|| J1) as col1, (A2 || B2 ||C2 .... ||J2 ) as col2 이렇게 됩니다.
- 수정전 : WHERE lv(+) <= length(str) - length(replace(str, ',', ''))
- 수정후 : WHERE lv(+) <= length(str) - length(replace(str, ',', '')) + 1
- 수정전 : REGEXP_SUBSTR(str, '[^|]+', 1, 1)
- 수정후 : REGEXP_SUBSTR(v, '[^|]+', 1, 1)
WITH temp AS
(
SELECT 'A1|A2|A3|A4 , B1|B2|B3|B4 , C1|C2|C3|C4' str FROM dual
UNION ALL SELECT 'F1|F2|F3|F4 , G1|G2|G3|G4' FROM dual
UNION ALL SELECT 'H1|H2|H3|H4' FROM dual
UNION ALL SELECT null FROM dual
)
SELECT rn, str
, WM_CONCAT(REGEXP_SUBSTR(v, '[^|]+', 1, 1)) v1
, WM_CONCAT(REGEXP_SUBSTR(v, '[^|]+', 1, 2)) v2
FROM (
SELECT rn, str
, REGEXP_SUBSTR(str, '[^, ]+', 1, lv) v
, lv
FROM (SELECT ROWNUM rn, str FROM temp)
, (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 99)
WHERE lv(+) <= length(str) - length(replace(str, ',', '')) + 1
order by lv asc
)
GROUP BY rn, str
PC 를 미는 바람에 테스트 없이 작성해 올렷더니.. 실수가 있었네요.
테스트 안해보고 올리면 항상 틀린다는...
wm_concat 는 정렬이 안되니...xmlagg 를 이용하세요.
WITH temp AS
(
SELECT 'A1|A2|A3|A4 , B1|B2|B3|B4 , C1|C2|C3|C4' str FROM dual
UNION ALL SELECT 'F1|F2|F3|F4 , G1|G2|G3|G4' FROM dual
UNION ALL SELECT 'H1|H2|H3|H4' FROM dual
UNION ALL SELECT null FROM dual
)
SELECT rn, str
, SUBSTR(XMLAgg(XMLElement(x, ',', REGEXP_SUBSTR(v, '[^|]+', 1, 1)) ORDER BY lv).Extract('//text()'), 2) v1
, SUBSTR(XMLAgg(XMLElement(x, ',', REGEXP_SUBSTR(v, '[^|]+', 1, 2)) ORDER BY lv).Extract('//text()'), 2) v2
FROM (SELECT rn, str
, REGEXP_SUBSTR(str, '[^, ]+', 1, lv) v
, lv
FROM (SELECT ROWNUM rn, str FROM temp)
, (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 99)
WHERE lv(+) <= length(str) - length(replace(str, ',', '')) + 1
)
GROUP BY rn, str
;
) 버튼을 클릭하여 작성 하시면 됩니다.