Home » Other » Marketplace » Department wise Comma Seprated Employee Ids
Department wise Comma Seprated Employee Ids [message #500026] Thu, 17 March 2011 05:13 Go to next message
nimishsoft
Messages: 10
Registered: March 2011
Location: Noida, India
Junior Member

I tried to get the way to get comma seprated values, except listagg or
wm_concat.
You may have a look on what I tried to get Department wise Comma Seprated Employee Ids.

http://nimishgarg.blogspot.com/2010/02/oracle-department-wise-employee-ids.html
Re: Department wise Comma Seprated Employee Ids [message #500039 is a reply to message #500026] Thu, 17 March 2011 06:13 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> col empnos format a40
SQL> with 
  2    data as (
  3      select deptno, empno,
  4             lag(empno) over(partition by deptno order by empno) prev_emp
  5      from emp
  6    )
  7  select deptno,
  8         substr(sys_connect_by_path(empno, ','), 2) empnos
  9  from data
 10  where connect_by_isleaf = 1
 11  connect by prior empno = prev_emp
 12  start with prev_emp is null
 13  order by deptno
 14  /
    DEPTNO EMPNOS
---------- ----------------------------------------
        10 7782,7839,7934
        20 7369,7566,7788,7876,7902
        30 7499,7521,7654,7698,7844,7900

Thanks to put my name and a link to this message on your blog.

Regards
Michel
Previous Topic: Fresher openings (Functional side)
Next Topic: Looking for Oracle BRM (Billing and Revenue Management) Consultant
Goto Forum:
  


Current Time: Thu Mar 28 19:47:36 CDT 2024