SQL Query that aggregates multiple rows into one row

Let Example.

I have a table that looks like this:

SELECT DEPTNO, EMPNO FROM EMP;

DEPTNO EMPNO

10         7839

30         7698

10         7782

20         7566

20         7788

20         7902

20         7369

30         7499

30         7521

30         7654

30         7844

20         7876

30         7900

10         7934

And I want to produce a result set that looks like this:

DEPTNO EMPNO

10         7839, 7782, 7934

20         7566, 7788, 7902, 7369, 7876

30         7698, 7499, 7521, 7654, 7844, 7900

Solution: = CREATE A FUNCTION

CREATE OR REPLACE FUNCTION GET_SEPARATED (INPUT_VAL IN NUMBER)

   RETURN VARCHAR2

IS

   RETURN_TEXT   VARCHAR2 (1000) := NULL;

BEGIN

   FOR X IN (SELECT EMPNO

               FROM EMP

              WHERE DEPTNO = INPUT_VAL)

   LOOP

      RETURN_TEXT:= RETURN_TEXT || X.EMPNO || ‘, ';

   END LOOP;

   RETURN RETURN_TEXT;

END;

  SELECT DEPTNO,

              GET_SEPARATED (DEPTNO)

  FROM EMP

  GROUP BY DEPTNO

DEPTNO EMPNO

10         7839, 7782, 7934

20         7566, 7788, 7902, 7369, 7876

30         7698, 7499, 7521, 7654, 7844, 7900