oracle pivot

LISTAGG : oracle 11g r2 에서 새로 추가된 함수. 기존에 존재 하던
pivot 함수( 또는 복잡한 SQL) 보다 간결하고 간단하게 구현하도록 제공하고 있다.

Syntax :
LISTAGG   ( <expr> [ ,  <delimiter> )   WITHIN  GROUP   ( ORDER BY  <oby_expression_list> )

 

그외에 다른 방법으로 사용 가능한 SQL

1. PL /SQL

 create or replace type t_vc as table of varchar2(4000);
/

create or replace function pivot return t_vc pipelined
as
  v_last_deptno emp.deptno%type := null;
  v_line  varchar2(4000);
begin
  for r in (select deptno , ename from emp order by deptno) loop
    if v_last_deptno is null then
      v_line:=rpad(r.deptno,11) || ': ';
      v_last_deptno := r.deptno;
    end if;
    if r.deptno <> v_last_deptno then
      pipe row(v_line);
      v_line:=rpad(r.deptno,11) || ': ';
      v_last_deptno := r.deptno;
    end if;
    v_line := v_line || rpad(r.ename,11);
  end loop;
  pipe row(v_line);
  return;
end pivot;
/

출력결과

SQL> select * from table(pivot);

COLUMN_VALUE
-----------------------------------------------------------------------------------------------------------------------------------
10    : CLARK KING    MILLER
20    : JONES FORD    ADAMS      SMITH  SCOTT
30    : WARD TURNER    ALLEN      JAMES  BLAKE     MARTIN

2. SQL ( DECODE 사용 )

SELECT   a.deptno
    ,        MAX(DECODE(a.r, 1,a.ename))
             || MAX(DECODE(a.r, 2,', ' || a.ename))
             || MAX(DECODE(a.r, 3,', ' || a.ename))
             || MAX(DECODE(a.r, 4,', ' || a.ename))
             || MAX(DECODE(a.r, 5,', ' || a.ename))
             || MAX(DECODE(a.r, 6,', ' || a.ename))
             || MAX(DECODE(a.r, 7,', ' || a.ename))
             || MAX(DECODE(a.r, 8,', ' || a.ename))
             || MAX(DECODE(a.r, 9,', ' || a.ename))
             || MAX(DECODE(a.r,10,', ' || a.ename)) names
   FROM    (SELECT t.deptno
            ,      t.ename
            ,      ROW_NUMBER()
                   OVER (PARTITION BY t.deptno
                         ORDER BY     NULL) r
            FROM   emp t) a
   GROUP BY a.deptno
   ORDER BY a.deptno
   /

출력결과

    DEPTNO NAMES
---------- ----------------------------------------------------------------------------------------------------------------------
 10 CLARK, KING, MILLER
 20 JONES, FORD, ADAMS, SMITH, SCOTT
 30 WARD, TURNER, ALLEN, JAMES, BLAKE, MARTIN

 3. SQL ( sys_connect_by_path )

select deptno,
 substr( max( sys_connect_by_path( ename, '; ') ), 3 ) names
 from ( select deptno, ename, row_number() over ( partition by deptno order by ename ) rn
   from emp )
 start with rn = 1
 connect by prior deptno = deptno and
   prior rn+1 = rn
 group by deptno
 order by deptno
/

출력결과

     DEPTNO NAMES
---------- ----------------------------------------------------------------------------------------------------
 10 CLARK; KING; MILLER
 20 ADAMS; FORD; JONES; SCOTT; SMITH
 30 ALLEN; BLAKE; JAMES; MARTIN; TURNER; WARD

참고자료 :

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:766825833740