What does LISTAGG do, and what replaces it in engines that do not have it?

## What string aggregation is for Ordinary aggregates collapse a group into a single scalar summary: `COUNT` gives a number, `SUM` gives a total, `MIN` gives one member. String aggregation is the aggregate that collapses a group without discarding its members — it returns one value per group that still contains *every* input value, joined together by a separator. It is how you turn ``` post_id | tag 1 | sql 1 | joins 2 | nulls ``` into one row per post whose `tags` column reads `joins, sql`. ## The standard: LISTAGG SQL:2016 defines `LISTAGG(<expression> [, <separator>]) WITHIN GROUP (ORDER BY <sort list>)`. Three parts matter: - **The expression** is evaluated once per row in the group; it is normally a character value or something implicitly cast to one. - **The separator** is a constant string placed *between* elements — never before the first or after the last. Omit it and the values are concatenated with nothing between them. - **`WITHIN GROUP (ORDER BY …)`** is the standard's syntax for an *ordered-set aggregate*: an aggregate whose result depends on the order in which the group's rows are fed to it. Rows in a group have no inherent order, so without this clause the element order would be arbitrary. ```sql SELECT post_id, LISTAGG(tag, ', ') WITHIN GROUP (ORDER BY tag) AS tags FROM post_tags GROUP BY post_id; ``` ## The dialect landscape This is one of the least portable corners of everyday SQL — the concept is universal, the spelling is not: - **Oracle, Db2** — `LISTAGG(expr, sep) WITHIN GROUP (ORDER BY …)`. - **SQL Server 2017 and later** — `STRING_AGG(expr, sep) WITHIN GROUP (ORDER BY …)`. - **PostgreSQL** — `string_agg(expr, sep ORDER BY …)`, with the ordering written inside the argument list rather than in a `WITHIN GROUP` clause. - **MySQL and MariaDB** — `GROUP_CONCAT(expr ORDER BY … SEPARATOR ', ')`; the separator is a keyword-introduced clause, and the default separator is a comma with no space. - **SQLite** — `group_concat(expr, sep)`, where the separator is simply the second argument. So a query that concatenates tags has to be rewritten when it moves between engines. If you need one statement that runs everywhere, the only fully portable fallback is to return one row per value and join them in the application. ## Arrays instead of strings Where the engine supports an array type, `ARRAY_AGG(expr [ORDER BY …])` collects the group's values into an array rather than a string. That is usually the better result shape when the consumer is code rather than a human report: elements keep their boundaries and their type, so a value that itself contains a comma cannot be mistaken for two values, and there is no parsing step on the client. A delimited string is the right choice when the output is a display column, an export, or a message; an array is the right choice when the caller will iterate the elements. Engines without an array type simply do not offer this, which is why the string form is the more commonly seen idiom. ## NULLs, empty groups, and delimiters Like other aggregates, the string forms **ignore NULL inputs**: a NULL contributes no element and no separator, so you never see an empty slot in the output. If you want NULLs represented, wrap the input: `STRING_AGG(COALESCE(city, 'unknown'), ', ')`. A group that contributes no non-null value at all yields **NULL**, not an empty string — so `COALESCE(STRING_AGG(...), '')` is the idiom when the caller wants `''` for the empty case. And because the separator sits only between elements, *n* values produce exactly *n − 1* separators. ## Where it is used and where it bites Typical uses are report columns ("tags per article", "roles per user"), building a human-readable audit line, and flattening a child table for an export. Two traps show up immediately in real work. The first is **order**: without an explicit ordering clause inside the aggregate, the element order is unspecified and can change between runs or plans. The second is **length**: every engine caps how long the aggregated result may be, and they disagree on whether exceeding the cap raises an error or silently truncates. A third, smaller one is **delimiter collision** — if a value can itself contain the delimiter, the string is ambiguous, and that is precisely the case where an array or plain rows is the sounder answer.