rowconcate Function

Start by creating this function:

SQL> CREATE OR REPLACE FUNCTION rowconcat(q in VARCHAR2) RETURN VARCHAR2 IS
  2    ret  VARCHAR2(4000);
  3    hold VARCHAR2(4000);
  4    cur  sys_refcursor;
  5  BEGIN
  6    OPEN cur FOR q;
  7    LOOP
  8      FETCH cur INTO hold;
  9      EXIT WHEN cur%NOTFOUND;
 10      IF ret IS NULL THEN
 11        ret := hold;
 12      ELSE
 13        ret := ret || ',' || hold;
 14      END IF;
 15    END LOOP;
 16    RETURN ret;
 17  END;
 18  /
Function created.

This function returns a string result with the concatenated non-NULL values from a SQL statement. It returns NULL if there are no non-NULL values.
Here is an example of how to map several rows to a single concatenated column:

SQL> SELECT rowconcat('SELECT dname FROM dept') AS departments
  2    FROM dual;
DEPARTMENTS
--------------------------------------------------------------------------------
ACCOUNTING,RESEARCH,SALES,OPERATIONS

This example is more interresting, it concatenates a column across several rows based on an aggregation:

SQL> col employees format a50
SQL> SELECT deptno,
  2         rowconcat('SELECT ename FROM emp a WHERE deptno='||deptno) AS Employees
  3    FROM emp
  4   GROUP BY deptno
  5  /
    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
        20 SMITH,JONES,SCOTT,ADAMS,FORD
        10 CLARK,KING,MILLER