ARRAY_AGG

The ARRAY_AGG function aggregates a set of elements into an array.

expression

An expression that returns a value that is any data type that can be specified for a CREATE TYPE (Array) statement.

ORDER BY

Specifies the order of the rows from the same grouping set that are processed in the aggregation. If the ORDER BY clause is not specified, or if the ORDER BY clause cannot differentiate the order of the sort key value, the rows in the same grouping set are arbitrarily ordered.

sort-key-expression: Specifies a sort key value that is either a column name or an expression. The data type of the column or expression must not be a DATALINK or XML value.; The ordering of the aggregated elements is based on the values of the sort keys.; The sum of the length attributes of the sort-key-expressions must not exceed 3.5 gigabytes.

If a collating sequence other than *HEX is in effect when the statement that contains the ARRAY_AGG function is executed and the sort-key-expressions are SBCS data, mixed data, or Unicode data, then the result is obtained by comparing weighted values. The weighted values are derived by applying the collating sequence to the sort-key-expressions.

The ARRAY_AGG function can only be specified within an SQL procedure or an SQL function in the following specific contexts:

The select-clause of a SELECT INTO statement
The select-clause of a scalar subquery on the right side of a SET statement

The SELECT that uses ARRAY_AGG cannot contain the DISTINCT clause.

Examples

Assume an array type and a table are created as follows:

CREATE TYPE PHONELIST AS DECIMAL(10,0) ARRAY[10]

CREATE TABLE EMPLOYEE (
    ID          INTEGER NOT NULL,
    PRIORITY    INTEGER NOT NULL,
    PHONENUMBER DECIMAL(10,0),
    PRIMARY KEY (ID, PRIORITY) )

Create a procedure that uses a SELECT INTO statement to return the prioritized list of contact numbers under which an employee can be reached.

CREATE PROCEDURE GETPHONENUMBERS
    (IN EMPID INTEGER,
     OUT NUMBERS PHONELIST)
  BEGIN
    SELECT ARRAY_AGG(PHONENUMBER ORDER BY PRIORITY) INTO NUMBERS
        FROM EMPLOYEE
        WHERE ID = EMPID;
  END

Create a procedure that uses a SET statement to return the list of an employee's contact numbers in an arbitrary order.

CREATE PROCEDURE GETPHONENUMBERS
    (IN EMPID INTEGER,
     OUT NUMBERS PHONELIST)
  BEGIN
    SET NUMBERS = 
      (SELECT ARRAY_AGG(PHONENUMBER)
        FROM EMPLOYEE
        WHERE ID = EMPID);
  END