Aggregating Multiple Row values into One column

This is pretty interesting and useful.

Sometimes, we might need to aggregate multiple row values into one single column. Oracle 9i provides xmlagg() function to achieve this.

Here is the employee table example which aggregates the multiple employee names into one column based on the department no.

select
   deptno,
   rtrim (xmlagg (xmlelement (e, ename ',')).extract ('//text()'), ',') enames
from
   emp
group by
   deptno
;
    DEPTNO ENAMES                                
---------- ----------------------------------------
        10 CLARK,MILLER,KING                     
        20 SMITH,FORD,ADAMS,SCOTT,JONES          
        30 ALLEN,JAMES,TURNER,BLAKE,MARTIN,WARD

Magic isn't it.

For more details, refer to the following URL: http://www.dba-oracle.com/t_converting_rows_columns.htm