[PGS9.2] 여러 행을 하나의 필드로 변환

/********************************************************************************************
-- Title : [PGS9.2] 여러 행을 하나의 필드로 변환
-- Reference : http://www.postgresonline.com/journal/archives/191-stringagg.html
-- Key word : pivot unpivot crosstab 멀티값 싱글값 array_to_string string_agg
********************************************************************************************/

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

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

-- LIST DATA
select * from ttt2;

-- CHANGE FORMAT
select col_main, array_to_string(array_agg(col_sub),'/')        /*9.0 이하*/
from ttt2
group by col_main;

select col_main, string_agg(col_sub,'/')                              /*9.1이상, 비정렬*/
from ttt2
group by col_main;

select col_main, string_agg(col_sub,'/' order by col_sub)     /*9.1이상, 정렬*/
from ttt2
group by col_main;

-- CLEAN TABLE
drop table ttt2;