Instead of Stragg /// Allow Duplicate

SELECT
deptno,
LTRIM(SYS_CONNECT_BY_PATH(ename, ','), ',') employees
FROM (
SELECT deptno,
ename,
row_number() over(PARTITION BY deptno ORDER BY deptno) rnum,
count(*) over(PARTITION BY deptno) tot
FROM scott.emp
)
WHERE rnum=tot
START WITH rnum = 1
CONNECT BY PRIOR rnum = rnum -1 AND PRIOR deptno = deptno;


select tb.deptno , substr ( sys_connect_by_path( tb.EMPNO, ',' ) , 2) as string
from ( select deptno ,EMPNO ,row_number() over ( partition by deptno
order by EMPNO ) as val_index from
scott.emp WHERE EMPNO IS NOT NULL ) tb where
connect_by_isleaf = 1 connect by val_index = prior val_index + 1 and
deptno = prior deptno start with val_index = 1 ;


WITH tab AS
(
SELECT &strg str
FROM DUAL)
SELECT REGEXP_SUBSTR (str, '[^,]+', 1, LEVEL) RESULT
FROM tab
CONNECT BY LEVEL <=
((SELECT SUM (CASE
WHEN SUBSTR (str, LEVEL, 1) = ','
THEN 1
WHEN LENGTH (str) = LEVEL
THEN 1
ELSE 0
END
)
FROM tab
CONNECT BY LEVEL <= LENGTH (str)));


SELECT REPLACE(&strg ,',',CHR(13)) FROM DUAL;


FROM 10G ONWARDS

SELECT deptno,wmsys.wm_concat(ename) d
FROM scott.emp group BY deptno

------------------------------------------
To remove duplicate  from a SQL

SELECT JOB, ENAME FROM
(SELECT DISTINCT ENAME,JOB, row_number () OVER (PARTITION BY ENAME ORDER BY ENAME) rn
FROM scott.emp )
WHERE rn = 1

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;