Map/ concatenate several rows to a column

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 interesting, 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