Concatenation on group by

Hi all,
As an extension to Chandan's previous post i would like to post this sample code which shows the usage of sys_connect_by_path operator in Oracle.

Using SYS_CONNECT_BY_PATH operator

Source table “temp”:

Name Deptno
------ -------
jagan 1
guru 2
varu 2
bharath 1
manju 1
giri 3
chandan 3

SELECT
deptno, substr(SYS_CONNECT_BY_PATH(name, ','),2) name_list
FROM
(
SELECT name,
deptno,
count(*) OVER ( partition by deptno ) cnt,
ROW_NUMBER () OVER ( partition by deptno order by name) seq
FROM temp
WHERE deptno is not null
)
WHERE
seq = cnt
START WITH
seq=1
CONNECT BY PRIOR
seq+1 = seq
AND PRIOR
deptno = deptno;

Result:
deptno Name_list
------- -------------
1 bharath,jagan,manju
2 guru,varun
3 chandan,giri