ARRAY_AGG

PostgreSQL

ARRAY_AGG(expression)

• expression: This is the specific column or set of data within the table that the ARRAY_AGG function is to be applied on. The function aggregates the data from the specified column into a single PostgreSQL array.

Example

SELECT department, ARRAY_AGG(employee_name)
FROM company
GROUP BY department;

Output

 department |       array_agg
------------+-----------------------
 finance    | {John, Jane, James}
 sales      | {Smith, Peter, Paul}

Explanation

In this example, the ARRAY_AGG function in PostgreSQL is used to concatenate the employee names into an array for each unique department in the “company” table. For each department, you are presented with an array of employee names associated with that department.

SQL Server

ARRAY_AGG( expression ) OVER ( [ partition_by_clause ] order_by_clause )

• expression: This is the column or expression that the ARRAY_AGG function will aggregate into a single array.
• partition_by_clause: This clause divides the result set produced by the FROM clause into partitions. The ARRAY_AGG function is applied to each partition separately and computation is reset for each partition.
• order_by_clause: This clause specifies the order in which rows are passed to the window function. The ARRAY_AGG function aggregates rows in the order that the ORDER BY clause specifies within each partition.

Example

The provided SQL code makes use of the ARRAY_AGG function:

SELECT ARRAY_AGG(colname)
FROM tablename;

Output

The output might be as follows:

------------------
| ARRAY_AGG      |
------------------
| [val1, val2]   |
------------------

Explanation

In the given example, ARRAY_AGG() is a SQL function utilized to convert a group of rows into a single array in PostgreSQL. Input is any column (colname) from a given table (tablename). The output is an array containing the values of the specified column from the table.