multiple row to 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, since it is the  must create function.

So, in some cases, this method can't be used. The advantage of this method is available in all oracle version.

SQL> 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; /    SQL> 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

출처 : http://dbmstuning.tistory.com/27