ORACLE 로우값들을 컬럼으로

ROW TO COLUMN

 

1. XMLAGG 사용

    select  deptno
              ,rtrim(xmlagg(xmlelement(e, ename || ',') order by ename).extract ('//text()'), ',') enames
        from emp

 group by deptno;

2. CONNECT BY 사용

  select deptno

        ,substr(SYS_CONNECT_BY_PATH(lname, ','),2) name_list
    from (
          select lname

                ,deptno

                ,count(*) OVER ( partition by deptno ) cnt

                ,ROW_NUMBER () OVER ( partition by deptno order by lname) seq
            from igribun.emp
           where deptno is not null

         ) 
   where seq=cnt
start with seq=1
connect by prior seq+1=seq and prior deptno=deptno;

 

출처:http://www.dba-oracle.com/t_converting_rows_columns.htm