SQL Grouping – Generating Comma Separated Lists

The following question appeared on the OTN forums (http://forums.oracle.com/forums/thread.jspa?messageID=3901658):

I want to select Multiple rows into a single line in ‘Single Column Table’ .

For ex:

Employee table has only one column , named as empname . it has three rows

Select empname from emp;

empname

——————————————————————————–
thambi
peter
antony

My expected result: thambi,peter,antony

i did see some post regarding pivot query but did not get righ post for Single Column table.. Can someone help me.

One way to do this is with analytic functions.  The set up:

CREATE TABLE T20(
  DEPT NUMBER(10),
  EMPNAME VARCHAR2(30));

INSERT INTO T20 VALUES (10,'THAMBI');
INSERT INTO T20 VALUES (10,'PETER');
INSERT INTO T20 VALUES (10,'ANTHONY');
INSERT INTO T20 VALUES (20,'GEORGE');
INSERT INTO T20 VALUES (20,'MICHAEL');

COMMIT;

COLUMN EMPNAME FORMAT A10

SELECT
  DEPT,
  EMPNAME,
  ROW_NUMBER() OVER (PARTITION BY DEPT ORDER BY EMPNAME DESC) RN
FROM
  T20;

 DEPT EMPNAME            RN
----- ---------- ----------
   10 THAMBI              1
   10 PETER               2
   10 ANTHONY             3
   20 MICHAEL             1
   20 GEORGE              2 

Now that the employees are separated by department, SYS_CONNECT_BY_PATH is used to draw the related employees into a single row:

COLUMN EMP_LIST FORMAT A30

SELECT
  DEPT,
  MAX(SUBSTR(SYS_CONNECT_BY_PATH(EMPNAME,','),2)) EMP_LIST
FROM
  (SELECT
    DEPT,
    EMPNAME,
    ROW_NUMBER() OVER (PARTITION BY DEPT ORDER BY EMPNAME DESC) RN
  FROM
    T20)
CONNECT BY PRIOR
  (DEPT||RN)=(DEPT||RN-1)
START WITH
  RN=1
GROUP BY
  DEPT;

 DEPT EMP_LIST
----- ------------------------------
   20 MICHAEL,GEORGE
   10 THAMBI,PETER,ANTHONY