SQL> CREATE TABLE t (
  2      dept        NUMBER
  3  ,   name        VARCHAR2(10)
  4  )
  5  /
 
Table created.
 
SQL> INSERT INTO t VALUES (10, 'Rao');
SQL> INSERT INTO t VALUES (10, 'Miller');
SQL> INSERT INTO t VALUES (10, 'Pezdek');
SQL> INSERT INTO t VALUES (10, 'Green');
SQL> INSERT INTO t VALUES (20, 'Zettinger');
SQL> INSERT INTO t VALUES (20, 'Patel');
SQL> INSERT INTO t VALUES (20, 'Goggin');
SQL> INSERT INTO t VALUES (30, 'Ramirez');
SQL> INSERT INTO t VALUES (30, 'Martin');
SQL> INSERT INTO t VALUES (40, 'Xing');
SQL> INSERT INTO t VALUES (40, 'Feeley');
SQL> INSERT INTO t VALUES (50, 'Justme');
 
SQL> SELECT   a.dept
  2  ,        MAX(DECODE(a.r, 1,a.name))
  3           || MAX(DECODE(a.r, 2,', ' || a.name))
  4           || MAX(DECODE(a.r, 3,', ' || a.name))
  5           || MAX(DECODE(a.r, 4,', ' || a.name))
  6           || MAX(DECODE(a.r, 5,', ' || a.name))
  7           || MAX(DECODE(a.r, 6,', ' || a.name))
  8           || MAX(DECODE(a.r, 7,', ' || a.name))
  9           || MAX(DECODE(a.r, 8,', ' || a.name))
 10           || MAX(DECODE(a.r, 9,', ' || a.name))
 11           || MAX(DECODE(a.r,10,', ' || a.name)) names
 12  FROM    (SELECT t.dept
 13           ,      t.name
 14           ,      ROW_NUMBER()
 15                  OVER (PARTITION BY t.dept
 16                        ORDER BY     NULL) r
 17           FROM   t) a
 18  GROUP BY a.dept
 19  ORDER BY a.dept
 20  /
 
      DEPT NAMES
---------- ----------------------------------------------------------------------------------------
        10 Rao, Miller, Pezdek, Green
        20 Zettinger, Patel, Goggin
        30 Ramirez, Martin
        40 Xing, Feeley
        50 Justme
 
SQL>