String Concat function-Oracle ( similar usage to SUM)

SELECT deptno, wm_concat(ename) AS employees

FROM   emp
GROUP BY deptno;

Result -----> 

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

Base Data:

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


For Oracle 9 --> as wm_concat() function is not available use following custom function. 
CREATE OR REPLACE FUNCTION get_it (invoice in VARCHAR2)
  RETURN VARCHAR2
IS
  l_text  VARCHAR2(32767) := NULL;
BEGIN
  FOR cur_rec IN (SELECT x AS itemName FROM invoice_breakdown where invoice_no = invoice /*query returning multiple rows*/) LOOP
    l_text := l_text || ',' || cur_rec.itemName;
  END LOOP;
  RETURN LTRIM(l_text, ',');
END;
/
SHOW ERRORS

Share Article : String Concat function-Oracle ( similar usage to SUM)

String Concat function-Oracle ( similar usage to SUM)