Posts Tagged ‘LISTAGG’
How to concaternate multiple rows into single row.
Sometimes, we have to concatenate multiple row values of a column into a single row.
Nowadays, I’m enjoying at the OTN forum. In the OTN forum, many people wonder about concatenation method.
So, I summarized all method that I know.
First of all, we have to create a table and data generation for the test.
create table dept_t (deptno number); insert into dept_t values(10); insert into dept_t values(20); insert into dept_t values(30); create table emp_t (empno number, ename varchar2(10), deptno number); insert into emp_t values(1,'A1',10); insert into emp_t values(2,'A2',10); insert into emp_t values(3,'A3',10); insert into emp_t values(4,'A4',10); insert into emp_t values(5,'B1',20); insert into emp_t values(6,'B2',20); insert into emp_t values(7,'C1',30); insert into emp_t values(8,'C2',30); insert into emp_t values(9,'C3',30); commit;
The result that we want is shown below. Ename must display in Empno in order.
deptno ename 10 A1 A2 A3 A4 20 B1 B2 30 C1 C2 C3
The First Method use User defined function.
create or replace function get_all_ename(p_deptno in number) return varchar2 is res varchar2(4000); i number:=0; v_ename emp_t.ename%TYPE; cursor c1 is select ename from emp_t where deptno=p_deptno order by empno; begin open c1; loop fetch c1 into v_ename; EXIT WHEN c1%NOTFOUND; res :=res||' '||v_ename; end loop; close c1; return res; end; / select get_all_ename(deptno) from dept_t; GET_ALL_ENAME(DEPTNO) -------------------------------------------------------------------------------- A1 A2 A3 A4 B1 B2 C1 C2 C3
The Second method use the XMLAGG. This method was introduced at oracle 9i.
select deptno, XMLAGG (XMLELEMENT ("ENAME", ename || ' ') order by empno).EXTRACT ('//text()') ename from emp_t group by deptno; DEPTNO ENAME ---------- ---------------------------------------- 10 A1 A2 A3 A4 20 B1 B2 30 C1 C2 C3
The Third method use the SYS_CONNECT_BY_PATH. This method was introduced at oracle 11gR1.
select deptno, max(SYS_CONNECT_BY_PATH(ename,' ')) ename from ( select ename, deptno, row_number() over (partition by deptno order by empno) rn from emp_t ) start with rn=1 connect by prior rn = rn-1 and prior deptno = deptno group by deptno order by deptno; DEPTNO ENAME ---------- ---------------------------------------- 10 A1 A2 A3 A4 20 B1 B2 30 C1 C2 C3
Forth method use LISTAGG. This method was introduced at oracle 11gR2.
select deptno, LISTAGG(ename, ' ') WITHIN GROUP (ORDER BY ename) ename from emp_t group by deptno; DEPTNO ENAME ---------- ---------------------------------------- 10 A1 A2 A3 A4 20 B1 B2 30 C1 C2 C3
Written by sean70kim
July 4, 2013 at 9:55 am
Posted in Oracle
Tagged with LISTAGG, SYS_CONNECT_BY_PATH, XMLAGG