Shape / table · All operations

Combine values into one cell per group

A variant of Summarise by group

Join each group's values into one delimited string or one list.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

The DuckDB step

Combine each group's values into one delimited string or one list

It reads the orders table the proofs share. Create it first:

SQL

CREATE TABLE orders AS SELECT * FROM (VALUES
  (1,  'Alice Ng',      'north', DATE '2026-01-03', 120.00, 'widget', 'a|b',   '{"channel":"web","items":[1,2]}'),
  (2,  'alice ng',      'north', DATE '2026-01-05',  80.50, 'gadget', 'b',     '{"channel":"shop","items":[3]}'),
  (3,  'Bob  Okafor',   'south', DATE '2026-01-05', 300.00, 'widget', 'a|c',   '{"channel":"web","items":[4,5,6]}'),
  (4,  'Carla Díaz',    'east',  DATE '2026-01-09',  NULL,  'gizmo',  NULL,    '{"channel":"web","items":[]}'),
  (5,  'Bob Okafor',    'south', DATE '2026-01-12',  45.25, 'gadget', 'c',     '{"channel":"phone","items":[7]}'),
  (6,  'Dev Patel',     'west',  DATE '2026-01-12', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (7,  'Carla Diaz',    'east',  DATE '2026-01-20',  99.99, 'gizmo',  'b|c',   '{"channel":"shop","items":[10]}'),
  (8,  'Dev Patel',     'west',  DATE '2026-02-02', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (9,  'Eve Larsen',    NULL,    DATE '2026-02-03',  15.00, 'gadget', NULL,    '{"channel":"web","items":[11]}'),
  (10, 'Alice Ng',      'north', DATE '2026-02-10', 220.00, 'gizmo',  'a|b|c', '{"channel":"phone","items":[12,13]}')
) t(order_id, customer, region, order_date, amount, product, tags, meta);

Then run the statement:

SQL

CREATE TEMP TABLE result AS
SELECT product,
       string_agg(customer, '; ' ORDER BY order_id) AS customers_text,
       list(order_id ORDER BY order_id) AS order_ids,
       list(DISTINCT customer ORDER BY customer) AS distinct_customers
FROM orders
GROUP BY product;
SELECT * FROM result ORDER BY product;
SELECT CASE WHEN ((SELECT customers_text FROM result WHERE product = 'widget') = 'Alice Ng; Bob  Okafor; Dev Patel; Dev Patel' AND (SELECT order_ids FROM result WHERE product = 'gizmo') = [4, 7, 10] AND (SELECT distinct_customers FROM result WHERE product = 'widget') = ['Alice Ng', 'Bob  Okafor', 'Dev Patel'] AND (SELECT count(*) FROM result) = 3) THEN 'as expected' ELSE error('each product did not come out as one string and one list of its customers and order ids') END AS verdict;

It needs nothing installed beyond DuckDB itself.
The statement checks its own result and raises an error when it is wrong: “each product did not come out as one string and one list of its customers and order ids”.
It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

Apache DataFusion (2)

array_aggread 2026-09-27
Aggregate: Array of the group values, optionally ordered or distinct
string_aggread 2026-09-27
Aggregate: Concatenate strings with a separator, optionally ordered

dbt-utils (1)

listaggread 2026-09-27
Aggregate concatenating group values into one delimited string, with order and limit

DuckDB (5)

array_aggread 2026-09-27
Returns a LIST containing all the values of a column.
group_concatread 2026-09-27
Concatenates the column string values with an optional separator.
listread 2026-09-27
Returns a LIST containing all the values of a column.
listaggread 2026-09-27
Concatenates the column string values with an optional separator.
string_aggread 2026-09-27
Concatenates the column string values with an optional separator.

Duckle (3)

Array Aggregateread 2026-09-25
collapse rows into a JSON array per group
Collect Listread 2026-09-25
collect values into a list per group
Denormalizeread 2026-09-25
collapse rows per group, joining values into delimited cells

EasyMorph (1)

Concatenate textread 2026-09-27
Join a column's values across rows, optionally grouped, or across columns, into delimited text

Ibis (2)

ArrayCollectread 2026-09-27
Collect the values of a column into one array
GroupConcatread 2026-09-27
Concatenate the strings of a group with a separator

OpenRefine (1)

Join multi-valued cells…read 2026-09-27
Concatenate the cells of a record's rows into its top cell with a separator

Polars (2)

Expr.str: vertical concatenation (group)read 2026-09-27
2 expressions: join, concat; join the strings of a column into one string, concat being deprecated for join
GroupBy.allread 2026-09-27
Collect the values of every group into list columns

Talend (2)

tDenormalizeread 2026-09-27
Concatenate the values of a column across each group of rows into one delimited value
tDenormalizeSortedRowread 2026-09-27
Combine each group of sorted rows into one row, joining distinct values with a separator

Trifacta (2)

LIST Functionread 2026-09-27
Collect the values of a column into an array
UNIQUE Functionread 2026-09-27
Collect the distinct values of a column into an array

Background

The listagg function, as defined in the SQL:2016 standard, aggregates data from multiple rows into a single concatenated string.^[1] The listagg function turns the values from a group of rows into a list of values delimited by a configurable separator.^[2] In PostgreSQL, array_agg collects all the input values, including nulls, into an array.^[3]

When to use it

Listagg is typically used to denormalise rows into a string of comma-separated values or a similar format suitable for human reading.^[2] Listagg does not apply any escaping, so it is not generally possible to tell whether a separator in the result is a real separator or part of a value.^[2] Safe use of listagg for electronic data interfaces is limited to cases where an unambiguous separator can be chosen, such as when aggregating numbers or dates.^[2] For electronic data interfaces, arrays and document types are advantageous because they offer type safety, or at least proper escaping.^[2]

In PostgreSQL, input rows are ordinarily fed to an aggregate in an unspecified order, and array_agg and string_agg produce results that depend on that order.^[4] Give the aggregate its own ORDER BY whenever anyone downstream will read the combined values in sequence. (Meridian's view) In PostgreSQL, string_agg concatenates only the non-null input values, while array_agg collects nulls along with the other values.^[3] In DuckDB, list does not return an empty list and string_agg does not return an empty string on an empty group; both return NULL.^[5] In DuckDB, a FILTER clause such as list(x) FILTER (WHERE x IS NOT NULL) excludes NULLs from list.^[5]

When DISTINCT is given to listagg, duplicate values are removed before aggregation, but the SQL standard does not specify which of the duplicates is removed.^[2] In DuckDB, every expression in the ORDER BY of a DISTINCT aggregate must also appear in the aggregate's argument list.^[5] In standard SQL, listagg accepts an ON OVERFLOW clause for a result longer than its return type allows, and the default is ON OVERFLOW ERROR.^[2]

The method

Listagg was introduced with SQL:2016 as optional feature T625.^[2] Listagg is an ordered-set function, so its order is given in a WITHIN GROUP clause, as in LISTAGG(expression, separator) WITHIN GROUP (ORDER BY ...).^[2] Listagg removes null values before aggregation, and its result is null if no non-null value remains.^[2]

In PostgreSQL, string_agg precedes each value after the first with the delimiter.^[3] In DuckDB, string_agg uses a comma as the separator when none is given, and group_concat and listagg are its aliases.^[5] In DuckDB, list returns a LIST containing all the values of a column, and array_agg is its alias.^[5] Array elements keep the type of the aggregated expression, so the application reading them can fetch the values in a type-safe way.^[2]

Compared with the alternatives

In DuckDB, unnesting turns a LIST-typed value into a table column, with each list element creating a row.^[6] Keep a list rather than a string when the values may need to go back into rows later. (Meridian's view) A table is in first normal form, defined by Edgar F. Codd, if each field is atomic, containing a single value rather than a set of values.^[7]

Sources

[1]Aggregate function - Wikipediasecondary source
[2]SQL LISTAGG concatenates values of multiple rows into an delimited string such as CSV.secondary source
[3]PostgreSQL: Documentation: 18: 9.21. Aggregate Functionsdocumentation
[4]PostgreSQL: Documentation: 18: 4.2. Value Expressionsdocumentation
[5]Aggregate Functions – DuckDBdocumentation
[6]Unnesting – DuckDBdocumentation
[7]First normal form - Wikipediasecondary source