Oracle SQL String Aggregate Functions: LISTAGG Guide

Formulas & functions · Updated November 22, 2026

Oracle SQL’s main string aggregate function is LISTAGG: it combines values from multiple rows into one delimited string, either for each group or across an analytic window. Use its WITHIN GROUP (ORDER BY ...) clause to control the order of the values; use ON OVERFLOW TRUNCATE when a result may exceed the supported return length.

What string aggregation means in Oracle

An aggregate function reduces several input rows to a single result. Numeric aggregates such as SUM return a number; string aggregation joins text values into one result. This is useful when a report needs one row per department with a comma-separated employee list, or when an application needs a readable summary rather than a separate row for every item.

In Oracle SQL, LISTAGG is the standard choice for ordinary delimited text. It is not the same as concatenating columns within one row: the || operator joins expressions from a single row, while LISTAGG combines an expression evaluated across multiple rows. Other Oracle features, including XMLAGG and JSON_ARRAYAGG, can be useful when the desired output is XML, JSON, or a large text value.

Basic LISTAGG syntax and example

Suppose an employees table has department_id and last_name columns. This query returns one row per department and alphabetizes the names within each result:

SELECT department_id,
       LISTAGG(last_name, ', ')
         WITHIN GROUP (ORDER BY last_name) AS employee_names
FROM employees
GROUP BY department_id;

The first argument, last_name, is the value to combine. The second argument, ', ', is the delimiter placed between values. The ordering clause sorts the values inside each aggregated string; it does not sort the department rows returned by the query. Add a query-level ORDER BY department_id if the output rows themselves also need a defined order.

If the delimiter is omitted, Oracle uses no delimiter. A delimiter is not added before the first value or after the last one. For a newline-separated list, a delimiter such as CHR(10) can be used, though how that line break appears depends on the client displaying the result.

Grouping, ordering, and analytic use

With GROUP BY, LISTAGG produces one result for each group. Every selected expression that is not aggregated must satisfy Oracle’s grouping rules, usually by appearing in the GROUP BY clause. For example, grouping by both department and job produces a different list for every department-and-job combination.

The order inside WITHIN GROUP matters because SQL does not promise a meaningful row order unless one is specified. If two values tie on the ordering expression, their relative order can be unpredictable. Add a unique tie-breaker, such as an employee identifier, when repeatable output matters:

LISTAGG(last_name, ', ')
  WITHIN GROUP (ORDER BY last_name, employee_id)

LISTAGG can also be used as an analytic function. In that form, the query keeps its detail rows and repeats the list for each row in the same partition, instead of collapsing each partition to one row:

SELECT department_id,
       employee_id,
       LISTAGG(last_name, ', ')
         WITHIN GROUP (ORDER BY last_name)
         OVER (PARTITION BY department_id) AS department_names
FROM employees;

This is useful when a report needs both individual records and their group’s combined text. Analytic aggregation does not replace a grouped query when the desired result is exactly one row per group.

NULL values, duplicates, and distinct lists

LISTAGG ignores null measure values. If a department has rows where last_name is null, those rows do not contribute an empty item or an extra delimiter. If every value in a group is null, the aggregate result is null. A null delimiter is treated as no delimiter.

Duplicates are included by default. To return each distinct value once, Oracle Database 19c and later support DISTINCT directly in the function:

LISTAGG(DISTINCT last_name, ', ')
  WITHIN GROUP (ORDER BY last_name)

On older database releases, remove duplicates in a subquery before aggregating. For example, select distinct department-and-name pairs in an inline view, then group that result and apply LISTAGG. This is safer than trying to remove duplicates from the finished string, where identical text may have come from different source rows or may contain the delimiter itself.

Overflow limits and truncation

A frequent surprise is that LISTAGG has a maximum return length. When the combined result exceeds that limit, Oracle normally raises ORA-01489: result of string concatenation is too long. The limit depends on the return type and database configuration: for a VARCHAR2 result, MAX_STRING_SIZE = STANDARD permits up to 4,000 bytes, while EXTENDED permits up to 32,767 bytes. These are byte limits, so a multibyte character set can reach the limit with fewer characters. Check the database configuration rather than assuming the larger limit is available.

Oracle Database 12.2 introduced overflow handling for LISTAGG. The truncation option returns a shortened result with an indicator instead of raising the usual overflow error:

LISTAGG(last_name, ', ' ON OVERFLOW TRUNCATE '...')
  WITHIN GROUP (ORDER BY last_name)

By default, the truncation option indicates how many values were omitted. You can specify WITH COUNT or WITHOUT COUNT to choose whether that count appears. Truncation is useful for display summaries, but it is not appropriate if every value must be preserved: use a larger-capacity output approach or return detail rows in that case. Also note that truncating a list can make it unsuitable as a complete data interchange format.

Alternatives: XMLAGG and JSON_ARRAYAGG

XMLAGG aggregates XML nodes rather than directly producing a delimited VARCHAR2. It is sometimes used to build a large text result when the usual LISTAGG length is insufficient. A commonly used pattern is:

SELECT department_id,
       RTRIM(
         XMLAGG(
           XMLELEMENT(e, last_name || ', ')
             ORDER BY last_name
         ).EXTRACT('//text()').GETCLOBVAL(),
         ', '
       ) AS employee_names
FROM employees
GROUP BY department_id;

This approach returns a CLOB from GETCLOBVAL, but it has XML-specific behavior and is less straightforward than LISTAGG. XML serialization can escape special characters, and trimming a delimiter must be handled carefully, especially if the data or delimiter has unexpected trailing characters. Test the result with values containing characters such as ampersands and angle brackets before relying on it. Use XML functions when XML processing or a CLOB result is genuinely useful, not merely as a complicated substitute for a short list.

JSON_ARRAYAGG is a better fit when the consumer needs a JSON array, not a plain delimited string. For example, it can represent values as JSON elements, preserving the distinction between separate values even if one value contains a comma. Its ordering is specified within the aggregate. JSON output is not interchangeable with a comma-separated string: applications should parse it as JSON, and available return-size options depend on the Oracle release.

Choosing the right approach

Choose based on the result the caller needs, the expected size, and whether the output must preserve structure:

  • LISTAGG is the clearest option for a short, human-readable delimited list, with explicit ordering and, on supported releases, duplicate removal or truncation.
  • JSON_ARRAYAGG suits application interfaces that expect JSON and need values kept as separate elements.
  • XMLAGG can help when XML is part of the workflow or a CLOB-sized aggregate is needed, but account for XML escaping and serialization.
  • Returning separate rows is often best when the list can become very large, callers need to filter or sort individual values, or truncating data would be unacceptable.

For a machine-readable format, avoid treating a delimiter as a reliable way to encode arbitrary text. If a name can itself contain a comma, a comma-separated list may be ambiguous unless the application defines an escaping or quoting rule. Structured JSON or separate rows are usually clearer choices.

Performance and common mistakes

Oracle may need to order the values within each group to satisfy WITHIN GROUP (ORDER BY ...). On large data sets, grouping and ordering can use significant memory or temporary space, so filter rows early when that is logically correct and avoid aggregating values that the result does not need. Indexes and execution plans can affect the work Oracle performs, but an index does not remove the need to verify the query’s actual plan and workload.

  • Do not assume the input row order determines the list order; put the required sort keys in WITHIN GROUP.
  • Do not confuse the aggregate’s ordering clause with the final query’s ORDER BY; they control different orders.
  • Do not expect duplicates to disappear unless you use DISTINCT where supported or deduplicate the input rows.
  • Do not assume a long result automatically becomes a CLOB. Standard LISTAGG returns a bounded character result and can raise an overflow error.
  • Do not use undocumented WM_CONCAT in new SQL. It is not a supported substitute for documented aggregate functions.

Version matters. Basic LISTAGG is available from Oracle Database 11g Release 2; overflow truncation arrived in 12.2, and the DISTINCT option arrived in 19c. Confirm the exact syntax against the documentation for the database release you deploy to, particularly when supporting multiple Oracle versions.

Frequently Asked Questions

How do I concatenate rows in Oracle SQL?
Use LISTAGG(value, delimiter) WITHIN GROUP (ORDER BY sort_column). Add GROUP BY to get one concatenated result per group.

Does LISTAGG include NULL values?
No. Null measure values are ignored. If every measure in a group is null, the result is null.

How do I remove duplicates from LISTAGG?
Use LISTAGG(DISTINCT value, delimiter) on Oracle Database 19c or later. On earlier releases, deduplicate the rows in a subquery before aggregation.

How do I fix ORA-01489 with LISTAGG?
For a display-only list, use ON OVERFLOW TRUNCATE on Oracle 12.2 or later. If no values may be lost, consider a CLOB-oriented approach such as carefully tested XMLAGG, or return the values as separate rows.

Can LISTAGG return a CLOB?
Ordinary LISTAGG returns a bounded VARCHAR2 result, or RAW when its measure is RAW; it does not become a CLOB simply because the input column is large. Consider XML or JSON aggregation options when their output type and structure fit the requirement.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.