COLLECT

Oracle

COLLECT(expression) RETURN collection_type

• expression: This parameter identifies the column or set of columns for which the COLLECT function will aggregate values. The expression can be any set of column names, expressions, constants, or function calls that resolve to a single value.
• return collection_type: This is an optional specifier that determines the data type of the returned nested table. If not specified, the result is a nested table of the expression’s data type.

Example

SELECT DEPARTMENT_ID, COLLECT(EMPLOYEE_ID) AS emp_id_collection
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID;

Output

DEPARTMENT_ID  |  emp_id_collection
---------------+------------------------------------------------
10             |  SYS.ODCINUMBERLIST(200, 201)
20             |  SYS.ODCINUMBERLIST(202, 203, 204)
30             |  SYS.ODCINUMBERLIST(205, 206)
40             |  SYS.ODCINUMBERLIST(207)

Explanation

The COLLECT function is used in the above SQL query to create a nested table of employee IDs for each department. The GROUP BY clause is used to separate the employees according to their department IDs.