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