SCOTT@10gXE> CREATE OR REPLACE FUNCTION concat_all_8i
  2    (p_columns_to_concat  IN VARCHAR2,
  3  	p_table_names	     IN VARCHAR2,
  4  	p_pivot_columns      IN VARCHAR2,
  5  	p_pivot_values	     IN VARCHAR2,
  6  	p_delimiter	     IN VARCHAR2 DEFAULT ', ',
  7  	p_where_clause	     IN VARCHAR2 DEFAULT '1=1',
  8  	p_order_by	     IN VARCHAR2 DEFAULT NULL)
  9    RETURN VARCHAR2
 10  AS
 11    TYPE ref_cursor_type  IS REF CURSOR;
 12    v_ref_cursor_variable	ref_cursor_type;
 13    v_value_to_concat	VARCHAR2 (4000);
 14    v_concatenated_values	VARCHAR2 (4000);
 15  BEGIN
 16    OPEN v_ref_cursor_variable FOR
 17  	   'SELECT '   || p_columns_to_concat
 18    || ' FROM '     || p_table_names
 19    || ' WHERE '    || p_pivot_columns || ' = :b_pivot_values'
 20    || ' AND ' || p_where_clause
 21    || ' ORDER BY ' || NVL (p_order_by, p_columns_to_concat)
 22    USING p_pivot_values;
 23    LOOP
 24  	 FETCH v_ref_cursor_variable INTO v_value_to_concat;
 25  	 EXIT WHEN v_ref_cursor_variable%NOTFOUND;
 26  	 v_concatenated_values := v_concatenated_values || p_delimiter || v_value_to_concat;
 27    END LOOP;
 28    CLOSE v_ref_cursor_variable;
 29    RETURN LTRIM (v_concatenated_values, p_delimiter);
 30  END concat_all_8i;
 31  /

Function created.

SCOTT@10gXE> SHOW ERRORS
No errors.
SCOTT@10gXE> COLUMN employees FORMAT A60 WORD_WRAPPED
SCOTT@10gXE> SELECT deptno,
  2  	    concat_all_8i ('ename', 'emp', 'deptno', deptno) AS employees
  3  FROM   emp
  4  GROUP  BY deptno
  5  ORDER  BY deptno
  6  /

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

SCOTT@10gXE> COLUMN "JOBS AND EMPLOYEES" FORMAT A60 WORD_WRAPPED
SCOTT@10gXE> SELECT deptno,
  2  	    concat_all_8i
  3  	      ('job || ''-'' || ename',
  4  	       'emp',
  5  	       'deptno',
  6  	       deptno) AS "JOBS AND EMPLOYEES"
  7  FROM   emp
  8  GROUP  BY deptno
  9  ORDER  BY deptno
 10  /

    DEPTNO JOBS AND EMPLOYEES
---------- ------------------------------------------------------------
        10 CLERK-MILLER, MANAGER-CLARK, PRESIDENT-KING
        20 ANALYST-FORD, ANALYST-SCOTT, CLERK-ADAMS, CLERK-SMITH,
           MANAGER-JONES

        30 CLERK-JAMES, MANAGER-BLAKE, SALESMAN-ALLEN, SALESMAN-MARTIN,
           SALESMAN-TURNER, SALESMAN-WARD


SCOTT@10gXE> COLUMN "DEPARTMENTS AND JOBS" FORMAT A20 WORD_WRAPPED
SCOTT@10gXE> SELECT deptno || ' ' || job AS "DEPARTMENTS AND JOBS",
  2  	    concat_all_8i
  3  	      ('ename || ''-'' || empno',
  4  	       'emp',
  5  	       'deptno || '' '' || job',
  6  	       deptno || ' ' || job) AS employees
  7  FROM   emp
  8  GROUP  BY deptno || ' ' || job
  9  ORDER  BY deptno || ' ' || job
 10  /

DEPARTMENTS AND JOBS EMPLOYEES
-------------------- ------------------------------------------------------------
10 CLERK             MILLER-7934
10 MANAGER           CLARK-7782
10 PRESIDENT         KING-7839
20 ANALYST           FORD-7902, SCOTT-7788
20 CLERK             ADAMS-7876, SMITH-7369
20 MANAGER           JONES-7566
30 CLERK             JAMES-7900
30 MANAGER           BLAKE-7698
30 SALESMAN          ALLEN-7499, MARTIN-7654, TURNER-7844, WARD-7521

9 rows selected.

SCOTT@10gXE> SELECT deptno || ' ' || job AS "DEPARTMENTS AND JOBS",
  2  	    concat_all_8i
  3  	      ('empno || ''-'' || ename',
  4  	       'emp',
  5  	       'deptno || '' '' || job',
  6  	       deptno || ' ' || job) AS employees
  7  FROM   emp
  8  GROUP  BY deptno || ' ' || job
  9  ORDER  BY deptno || ' ' || job
 10  /

DEPARTMENTS AND JOBS EMPLOYEES
-------------------- ------------------------------------------------------------
10 CLERK             7934-MILLER
10 MANAGER           7782-CLARK
10 PRESIDENT         7839-KING
20 ANALYST           7788-SCOTT, 7902-FORD
20 CLERK             7369-SMITH, 7876-ADAMS
20 MANAGER           7566-JONES
30 CLERK             7900-JAMES
30 MANAGER           7698-BLAKE
30 SALESMAN          7499-ALLEN, 7521-WARD, 7654-MARTIN, 7844-TURNER

9 rows selected.

SCOTT@10gXE> COLUMN "DEPARTMENTS AND EMPLOYEES" FORMAT A60 WORD_WRAPPED
SCOTT@10gXE> SELECT e.job,
  2  	    concat_all_8i
  3  	      ('d.dname || ''-'' || e.ename',
  4  	       'emp e, dept d',
  5  	       'e.job',
  6  	       e.job,
  7  	       ', ',
  8  	       'd.deptno=e.deptno') AS "DEPARTMENTS AND EMPLOYEES"
  9  FROM   emp e, dept d
 10  WHERE  d.deptno = e.deptno
 11  GROUP  BY e.job
 12  ORDER  BY e.job
 13  /

JOB       DEPARTMENTS AND EMPLOYEES
--------- ------------------------------------------------------------
ANALYST   RESEARCH-FORD, RESEARCH-SCOTT
CLERK     ACCOUNTING-MILLER, RESEARCH-ADAMS, RESEARCH-SMITH,
          SALES-JAMES

MANAGER   ACCOUNTING-CLARK, RESEARCH-JONES, SALES-BLAKE
PRESIDENT ACCOUNTING-KING
SALESMAN  SALES-ALLEN, SALES-MARTIN, SALES-TURNER, SALES-WARD

SCOTT@10gXE>