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
antonyMy 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
Leave a Reply