COLLECT

COLLECT is an aggregate function in SQL that converts a set of rows into a nested table. It enables manipulation of the table as a single unit, rather than processing each row individually. This can enhance performance for large datasets, making it a useful tool in managing complex SQL tasks.

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.

For in-depth explanations and examples SQL keywords where you write your SQL, install our extension.