-- Title : [ORA10g] 멀티값을 싱글값으로 변경
-- Reference : blog.naver.com/cjj721004, dbrang.tistory.com
-- Key word : 멀티값, 싱글값, pivot, unpivot, multi row single row 멀티행 싱글행 crosstab
sys_connect_by_path start with connect by xmlagg xmlelement xml
********************************************************************************************/
-- 테이블 삭제
DROP TABLE scott.ttt;
-- 테이블 생성
CREATE TABLE scott.ttt
( seq NUMBER NOT NULL
, nm VARCHAR(10) NOT NULL
);
-- 데이터 입력
INSERT INTO scott.ttt
SELECT 1, '유재석' FROM dual UNION ALL
SELECT 1, '정형돈' FROM dual UNION ALL
SELECT 1, '노홍철' FROM dual UNION ALL
SELECT 1, '정준하' FROM dual UNION ALL
SELECT 2, '하하' FROM dual UNION ALL
SELECT 2, '길' FROM dual UNION ALL
SELECT 2, '박명수' FROM dual;
-- ROW_NUMBER() 반영
SELECT ROW_NUMBER() OVER(ORDER BY seq) AS rnum
, seq, nm
FROM scott.ttt;
/**************************************************
-- SYS_CONNECT_BY_PATH로 구현
-- XMLAGG, XMLELEMENT로 구현한 것보다 빠르다.
**************************************************/
-- SYS_CONNECT_BY_PATH 반영
SELECT a.seq, substr(sys_connect_by_path(a.nm, '|'), 2) AS nm2
, a.rnum
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY seq
ORDER BY seq
) AS rnum
, seq, nm
FROM scott.ttt
) a
START WITH a.rnum = 1
CONNECT BY PRIOR a.rnum = a.rnum - 1 AND PRIOR a.seq = a.seq;
-- GROUP BY로 그룹핑
SELECT a.seq, MAX(substr(sys_connect_by_path(a.nm, '|'), 2)) AS nm2
--, a.rnum
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY seq
ORDER BY seq
) AS rnum
, seq, nm
FROM scott.ttt
) a
START WITH a.rnum = 1
CONNECT BY PRIOR a.rnum = a.rnum - 1 AND PRIOR a.seq = a.seq
GROUP BY a.seq;
-- XMLAGG, XMLELEMENT로 구현
-- SYS_CONNECT_BY_PATH로 구현한 것보다 느리다.
****************************************************/
SELECT seq
, SUBSTR(a.nm, 2) "NM"
FROM
(
SELECT seq,
XMLAGG(XMLELEMENT(X, ','||nm||'') ORDER BY nm).EXTRACT('//text()').GETSTRINGVAL() "NM"
FROM scott.ttt
GROUP BY seq
) a
/**************************************************/
-- 8i -- -- 9i -- -- 10G -- |