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