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

Level queries

To get the higher manager of a particular employee


select yy.empno,yy.mgr,yy.ename,rt.empno,rt.mgr,rt.ename from
(select * from emp t
connect by prior t.empno=t.mgr start with mgr is null) yy,
emp rt
where yy.empno=rt.mgr --and rt.empno=7782

-------------------------------------------------------------------------------

To get repeating data

select deptno,dname,loc,ltrim(ename,' ,') ename,ltrim(hiredate,' ,') hiredate,ltrim(mgr,' ,') mgr
,ltrim(job,' ,') job
from (
select deptno,dname,loc,SYS_CONNECT_BY_PATH (ename,' ,') ename,SYS_CONNECT_BY_PATH (hiredate,' ,') hiredate,
SYS_CONNECT_BY_PATH (job,' ,') job,SYS_CONNECT_BY_PATH (mgr,' ,') mgr
from (
select r.deptno,r.dname,r.loc,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm ,
row_number() over (partition by r.deptno order by r.deptno) r,
row_number() over (partition by r.deptno order by r.deptno) -1 c
from scott.dept r,scott.emp e
where r.deptno=10 and e.deptno=r.deptno
) start with r = 1
connect by c = prior r
order by rownum desc)
where rownum = 1 ;


OR


select r.deptno,r.dname,r.loc,wmsys.wm_concat(e.ename),wmsys.wm_concat(e.job),wmsys.wm_concat(e.mgr),wmsys.wm_concat(e.hiredate),wmsys.wm_concat(e.sal),wmsys.wm_concat(e.comm)
from scott.dept r,scott.emp e
where r.deptno=10 and e.deptno=r.deptno
group by r.deptno,r.dname,r.loc

Alphabets,Level, Analytic Query

Alphabets

WITH AlphaBet AS
( SELECT CHR((ROWNUM + 64)) AS Character
FROM DUAL CONNECT BY level <= 157)
SELECT * FROM AlphaBet;

------------------------------------------------------------------------
Some Analytic Functions

SELECT G.*,ROW_NUMBER() OVER(PARTITION BY G.DEPTNO ORDER BY G.EMPNO)
FROM EMP G;

SELECT G.DEPTNO,G.ENAME,SUM(G.SAL) OVER (PARTITION BY G.DEPTNO ORDER BY G.DEPTNO) FROM EMP G ;

SELECT FIRST_VALUE(G.EMPNO) OVER() FROM EMP G;

---------------------------------------------------------------------------
Level Query


select level,lpad(' ',3*(level)) || VC_designation s,nn_desgcode dsg,nn_repdesgcode rcd,
SYS_CONNECT_BY_path (nn_repdesgcode,'/') op,
substr(SYS_CONNECT_BY_path (nn_repdesgcode,'/'),3,
length((SYS_CONNECT_BY_path (nn_repdesgcode,'/')))) hjdd
from hr_designation_mast where nn_repdesgcode is not null
start with nn_repdesgcode is not null
connect by prior nn_desgcode=nn_repdesgcode