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
