SQL Grouping – Generating Comma Separated Lists

30 11 2009

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

Actions

Information

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

Join 140 other followers

%d bloggers like this: