[ORA10g] 멀티행을 싱글값으로 변환

/********************************************************************************************
-- Title : [ORA10g] 멀티행을 싱글값으로 변환
-- Reference : 웹검색
-- Key word : pivot unpivot crosstab 멀티값 싱글값 멀티행 싱글행
********************************************************************************************/

-- CREATE TABLE
create table ttt2
( col_main varchar2(20)
, col_sub varchar2(10)
);

-- INSERT DATA
insert into ttt2
select '무한도전', '유재석' from dual union all
select '무한도전', '박명수' from dual union all
select '무한도전', '정준하' from dual union all
select '무한도전', '정현돈' from dual union all
select '무한도전', '노홍철' from dual union all
select '무한도전', '하하' from dual union all
select '무한도전', '길' from dual;

commit;

-- CHANGE FORMAT
select col_main, wm_concat(col_sub)
from ttt2
group by col_main;

-- CLEAN TABLE
drop table ttt2 purge;

 

 

/**************************************************/

출처 : http://www.oracleclub.com/article/21720

-- 8i -- SELECT num      , MIN(DECODE(rn,1,''''||data||''''))     || MIN(DECODE(rn,2,','''||data||''''))     || MIN(DECODE(rn,3,','''||data||''''))   FROM (SELECT num, data              , ROW_NUMBER() OVER(PARTITION BY num ORDER BY data) rn           FROM t         )  GROUP BY num ;-- 9i -- SELECT num      , SUBSTR(MAX(SYS_CONNECT_BY_PATH(''''||data||'''',',')),2) data   FROM (SELECT num, data              , ROW_NUMBER() OVER(PARTITION BY num ORDER BY data) rn           FROM t         )  START WITH rn = 1  CONNECT BY PRIOR num = num         AND PRIOR rn = rn - 1  GROUP BY num ;-- 10G -- SELECT num      , REPLACE(SUBSTR(data,2),'@','''') data   FROM ( SELECT num, XMLAGG(XMLELEMENT(x,',@'||data||'@') ORDER BY data).EXTRACT('//text()').GetStringVal() data   FROM t  GROUP BY num ) ;