Converting Multiple Row data into single row data

In some cases we need to have a result which aggregates multiple row data into a single row while displaying the result.

For example:

     When we want to show all the employee names working under a department as a single row we can use the below queries

LISTAGG

LISTAGG function is introduced in Oracle 11g R2.

{code}

SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS 

employees FROM emp GROUP BY deptno;

{/code}

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10   CLARK,KING,MILLER
        20   ADAMS,FORD,JONES,SCOTT,SMITH
        30   ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

 SYS_CONNECT_BY_PATH

We can use SYS_CONNECT_BY_PATH in releases prior to 11g as shown below

 For example consider the below data set

with t as
(select 'PFL' content, 0 i,110 rUID from dual union all
select 'LHL', 0 ,111 from dual union all
select 'PHL', 1, 111 from dual union all
select 'CHL', 2, 111 from dual union all
select 'DHL', 0, 112 from dual union all
select 'VHL', 1, 112 from dual union all
select 'CPHL', 0, 114 from dual union all
select 'WDCL', 1, 114 from dual union all
select 'AHL' ,2 ,114 from dual union all
select 'NFDL', 3, 114 from dual)

If we want the results grouped within its rUID then we can use the below query

Content                               rUID
-------------------------------     -----------    
PFL                                   110
LHL PHL CHL                           111
DHL VHL                               112
CPHL WDCL AHL NFDL                    114

{code}

select trim(sys_connect_by_path(content,' ')) as content, rUID
  from t
 where connect_by_isleaf = 1
 connect by rUID = prior rUID and i = prior i+1
 start with i = 0 

{/code} 

we can use WM_CONCAT(undocumented) function before 11g R2 release. 

From 9i onwards 

select 
id,
rtrim (xmlagg (xmlelement (e, value || ',')).extract ('//text()'), ',') values
from test_table
group by id;

Refer link http://www.oracle-base.com/articles/misc/StringAggregationTechniques.php for more solutions