COLLECT

COLLECT is a SQL function, introduced with Oracle 10g, that aggregates data into a nested table collection, retaining multiple records of data within a single row.

Examples

Simple example, collecting the employee names in each department:

SQL> SELECT deptno, COLLECT(ename)
  2    FROM emp
  3   GROUP BY deptno;
    DEPTNO COLLECT(ENAME)
---------- -----------------------------------------------------------------
        10 SYSTPX9GB4fvXcdHgRAADugLXNg==('CLARK', 'KING')
        20 SYSTPX9GB4fvXcdHgRAADugLXNg==('SMITH', 'JONES', 'SCOTT')
        30 SYSTPX9GB4fvXcdHgRAADugLXNg==('ALLEN', 'WARD', 'MARTIN', 'BLAKE')

Using a user defined collection type:

SQL> CREATE OR REPLACE TYPE varchar2s AS TABLE OF VARCHAR2(2000);
  2  /
Type created.

SQL> SELECT deptno, CAST(COLLECT(ename) AS varchar2s)
  2    FROM emp
  3   GROUP BY deptno;
    DEPTNO CAST(COLLECT(ENAME)ASVARCHAR2S)
---------- -----------------------------------------------------------------
        10 VARCHAR2S('CLARK', 'KING')
        20 VARCHAR2S('SMITH', 'JONES', 'SCOTT')
        30 VARCHAR2S('ALLEN', 'WARD', 'MARTIN', 'BLAKE')