Database Scripts

Beta
 Log In    |   Sign Up

Oracle : Concatenate multiple rows in one row

Added on Apr-17-2014 by Jelong
For Oracle

Tags : concatenatelistaggwm_concat

Concatenate multiple rows in a row by using LISTAGG() in Oracle 11.2 or wmsys.wm_concat() function in Oracle 10 version

Versions

Oracle 11g, Oracle 10g

DOWNLOAD

-- Example 1.  LISTAGG() within group() function in Oracle 11.2
SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
FROM   emp
GROUP BY deptno;

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
		
-- Example 2. (under Ora11.2 version) wmsys.wm_concat() function
SELECT deptno, wm_concat(ename) AS employees
FROM   emp
GROUP BY deptno;

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,FORD,ADAMS,SCOTT,JONES
        30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD

-- Example 3. Simple way after Oracle 10g
SELECT deptno, SUBSTR (SYS_CONNECT_BY_PATH (ename , ','), 2) csv
FROM   (SELECT deptno, ename, 
               ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename ) rn,
               COUNT (*) OVER (PARTITION BY deptno) cnt
          FROM emp)
WHERE rn = cnt
START WITH rn = 1
CONNECT BY  deptno = PRIOR deptno AND rn = PRIOR rn + 1 ;

    DEPTNO CSV
---------- ------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
        40 JPARK

    

Report Script

blog comments powered by Disqus