To get diff data 's of similar id in a row

-----------

create or replace type string_agg_type as object (
total varchar2(4000),

static function
ODCIAggregateInitialize(sctx IN OUT string_agg_type )
return number,

member function
ODCIAggregateIterate(self IN OUT string_agg_type ,
value IN varchar2 )
return number,

member function
ODCIAggregateTerminate(self IN string_agg_type,
returnValue OUT varchar2,
flags IN number)
return number,

member function
ODCIAggregateMerge(self IN OUT string_agg_type,
ctx2 IN string_agg_type)
return number
);
/

create or replace type body string_agg_type
is

static function ODCIAggregateInitialize(sctx IN OUT string_agg_type)
return number
is
begin
sctx := string_agg_type( null );
return ODCIConst.Success;
end;

member function ODCIAggregateIterate(self IN OUT string_agg_type,
value IN varchar2 )
return number
is
begin
self.total := self.total || ',' || value;
return ODCIConst.Success;
end;

member function ODCIAggregateTerminate(self IN string_agg_type,
returnValue OUT varchar2,
flags IN number)
return number
is
begin
returnValue := ltrim(self.total,',');
return ODCIConst.Success;
end;

member function ODCIAggregateMerge(self IN OUT string_agg_type,
ctx2 IN string_agg_type)
return number
is
begin
self.total := self.total || ctx2.total;
return ODCIConst.Success;
end;

end;
/

CREATE or replace
FUNCTION stragg(input varchar2 )
RETURN varchar2
PARALLEL_ENABLE AGGREGATE USING string_agg_type;
/

select deptno, stragg(ename)
from scott.emp
group by deptno
/

From 10g onwards No need for user defined function and type , We can use Oracle supplied function

select deptno, wmsys.wm_concat(ename)
from scott.emp group by deptno

In Oracle 10g  wm_concat is an oracle undocumnented function 
 
From 11g onwards , We can use Oracle supplied listlagg function .

select distinct listagg(orcl, ',')   within group(order by orcl) over(partition by 1) orcl  from
 ( select 'ORACLE9i' orcl from dual
 union
 select 'ORACLE10g' from dual
union select 'ORACLE11g' from dual )  c ;

Instead of wm_concat  in Oracle 10 g

SELECT DEPARTMENT_ID , RTRIM(XMLAGG(XMLELEMENT(E, FIRST_NAME || ',')).EXTRACT('//text()'), ',') ENAMES FROM EMPLOYEES GROUP BY DEPARTMENT_ID ;

 SELECT SUBSTR(SYS_CONNECT_BY_PATH(username, ';'), 2) csv FROM (SELECT username, ROW_NUMBER() OVER(ORDER BY username) rn, COUNT(*) OVER() cnt FROM all_users where rownum <= 3) WHERE rn = cnt START WITH rn = 1 CONNECT BY rn = PRIOR rn + 1;