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

Split values ;///; Pyramid

To split values

CREATE OR REPLACE Function Tokenizer(p_string In VarChar2,
p_separators In VarChar2)
Return varchar2_table Pipelined
Is
v_strs dbms_sql.varchar2s;
Begin
/*SELECT Tokenizer('1E030N,898989,6565,3535,67676',',') FROM DUAL;*/
With sel_string As (Select p_string fullstring From dual)
Select substr(fullstring, beg+1, end_p-beg-1) token
Bulk Collect Into v_strs
From (Select beg, Lead(beg) Over (Order By beg) end_p, fullstring
From (Select beg, fullstring
From (Select Level beg, fullstring
From sel_string
Connect By Level <= length(fullstring)
)
Where instr(p_separators,substr(fullstring,beg,1)) >0
Union All
Select 0, fullstring
From sel_string
Union All
Select length(fullstring)+1, fullstring
From sel_string)
)
Where end_p Is Not Null
And end_p > beg + 1;
For i In v_strs.first..v_strs.last Loop
PIPE ROW(v_strs(i));
End Loop;
RETURN;
End Tokenizer;

----------------------------------------------------------------
Check this query

select wmsys.wm_concat(case when Rn <=46 then Rn end) over(order by Rn) str
from (select RowNum as rn from all_catalog where RowNum <= 46);