[퀴즈] 구분자로 나누어 행,열 바꾸기
이번 퀴즈로 배워보는 SQL 시간에는 구분자로 데이터를 분할하고, 행과 열을 바꾸어 출력하는 SQL 문제를 풀어본다. 지면 특성상 문제와 정답 그리..
www.gurubee.net
-- 구분자로 나누어 행, 열 바꾸기
WITH TEST AS
(
SELECT 1 NO, '1:10|2:11|3:12|4:15' V FROM DUAL
UNION ALL SELECT 2, '1:17|3:15|4:25' FROM DUAL
UNION ALL SELECT 3, '2:11|4:15' FROM DUAL
UNION ALL SELECT 4, '1:10|2:21|4:19' FROM DUAL
)
SELECT *
FROM TEST
;
WITH TEST AS
(
SELECT 1 NO, '1:10|2:11|3:12|4:15' V FROM DUAL
UNION ALL SELECT 2, '1:17|3:15|4:25' FROM DUAL
UNION ALL SELECT 3, '2:11|4:15' FROM DUAL
UNION ALL SELECT 4, '1:10|2:21|4:19' FROM DUAL
)
SELECT NO
, SUBSTR(V, 1, INSTR(V, '|') -1) V1 -- SUBSTR([문장], 시작위치[NUMBER], 자르고 싶은 길이[NUMBER])
, SUBSTR(V, INSTR(V, '|', 1, 1) + 1
, INSTR(V, '|', 1, 2) - INSTR(V, '|', 1, 1) -1
) V2
, SUBSTR(V, INSTR(V, '|', 1, 2) + 1
, INSTR(V, '|', 1, 3) - INSTR(V, '|', 1, 2) -1
) V3
, SUBSTR(V, INSTR(V, '|', 1, 3) + 1 ) V4
FROM TEST
;
-- 결과도 이상하고 내용도 복잡하다
WITH TEST AS
(
SELECT 1 NO, '1:10|2:11|3:12|4:15' V FROM DUAL
UNION ALL SELECT 2, '1:17|3:15|4:25' FROM DUAL
UNION ALL SELECT 3, '2:11|4:15' FROM DUAL
UNION ALL SELECT 4, '1:10|2:21|4:19' FROM DUAL
)
SELECT NO
, REGEXP_SUBSTR(V, '1:[^|]+') "1"
, REGEXP_SUBSTR(V, '2:[^|]+') "2"
, REGEXP_SUBSTR(V, '3:[^|]+') "3"
, REGEXP_SUBSTR(V, '4:[^|]+') "4"
FROM TEST
;
-- 정규식을 이용하면 좀 더 간편하게 데이터 분리가 가능하다
WITH TEST AS
(
SELECT 1 NO, '1:10|2:11|3:12|4:15' V FROM DUAL
UNION ALL SELECT 2, '1:17|3:15|4:25' FROM DUAL
UNION ALL SELECT 3, '2:11|4:15' FROM DUAL
UNION ALL SELECT 4, '1:10|2:21|4:19' FROM DUAL
)
SELECT NO
, SUBSTR(REGEXP_SUBSTR(V, '1:[^|]+'), 3) V1
, SUBSTR(REGEXP_SUBSTR(V, '2:[^|]+'), 3) V2
, SUBSTR(REGEXP_SUBSTR(V, '3:[^|]+'), 3) V3
, SUBSTR(REGEXP_SUBSTR(V, '4:[^|]+'), 3) V4
FROM TEST
;
WITH TEST AS
(
SELECT 1 NO, '1:10|2:11|3:12|4:15' V FROM DUAL
UNION ALL SELECT 2, '1:17|3:15|4:25' FROM DUAL
UNION ALL SELECT 3, '2:11|4:15' FROM DUAL
UNION ALL SELECT 4, '1:10|2:21|4:19' FROM DUAL
)
SELECT *
FROM
(
SELECT NO
, SUBSTR(REGEXP_SUBSTR(V, '1:[^|]+'), 3) V1
, SUBSTR(REGEXP_SUBSTR(V, '2:[^|]+'), 3) V2
, SUBSTR(REGEXP_SUBSTR(V, '3:[^|]+'), 3) V3
, SUBSTR(REGEXP_SUBSTR(V, '4:[^|]+'), 3) V4
FROM TEST
)
UNPIVOT (V FOR GB IN (V1, V2, V3, V4))
;
WITH TEST AS
(
SELECT 1 NO, '1:10|2:11|3:12|4:15' V FROM DUAL
UNION ALL SELECT 2, '1:17|3:15|4:25' FROM DUAL
UNION ALL SELECT 3, '2:11|4:15' FROM DUAL
UNION ALL SELECT 4, '1:10|2:21|4:19' FROM DUAL
)
SELECT gb
, MIN(DECODE(NO, 1, V)) "1"
, MIN(DECODE(NO, 2, V)) "2"
, MIN(DECODE(NO, 3, V)) "3"
, MIN(DECODE(NO, 4, V)) "4"
FROM
(
SELECT NO
, SUBSTR(REGEXP_SUBSTR(V, '1:[^|]+'), 3) "1"
, SUBSTR(REGEXP_SUBSTR(V, '2:[^|]+'), 3) "2"
, SUBSTR(REGEXP_SUBSTR(V, '3:[^|]+'), 3) "3"
, SUBSTR(REGEXP_SUBSTR(V, '4:[^|]+'), 3) "4"
FROM TEST
)
UNPIVOT (V FOR GB IN ("1", "2", "3", "4"))
GROUP BY GB
ORDER BY gb
;
-- 최종 쿼리
WITH TEST AS
(
SELECT 1 NO, '1:10|2:11|3:12|4:15' V FROM DUAL
UNION ALL SELECT 2, '1:17|3:15|4:25' FROM DUAL
UNION ALL SELECT 3, '2:11|4:15' FROM DUAL
UNION ALL SELECT 4, '1:10|2:21|4:19' FROM DUAL
)
SELECT *
FROM
(
SELECT NO
, SUBSTR(REGEXP_SUBSTR(V, '1:[^|]+'), 3) "1"
, SUBSTR(REGEXP_SUBSTR(V, '2:[^|]+'), 3) "2"
, SUBSTR(REGEXP_SUBSTR(V, '3:[^|]+'), 3) "3"
, SUBSTR(REGEXP_SUBSTR(V, '4:[^|]+'), 3) "4"
FROM TEST
)
UNPIVOT (V FOR GB IN ("1", "2", "3", "4" ))
PIVOT (MIN(V) FOR NO IN (1,2,3,4))
;