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
