Oracle’s main string aggregation function is LISTAGG: it combines values from multiple rows into one delimited string, with an explicit order. For larger results or different output formats, Oracle developers may use XMLAGG or JSON_ARRAYAGG, but those produce XML or JSON structures rather than serving as direct substitutes for a plain delimited string.
Which Oracle functions aggregate strings?
For a comma-separated list or another ordinary text summary, start with LISTAGG. It is an ordered-set aggregate: the values being combined are rows in a group, and the ordering that determines their position is specified inside WITHIN GROUP. It can also operate analytically, returning a list for a partition of rows without collapsing those rows into one result.
Other Oracle functions can help when the output has a different purpose:
XMLAGGaggregates XML fragments into anXMLType. It can be part of a strategy for producing long text, but serialization and XML escaping need attention.JSON_ARRAYAGGaggregates values into a JSON array. Choose it when consumers need structured JSON, not when they expect a conventional comma-delimited string.COLLECTaggregates values into a collection. It is useful for collection-oriented processing, but it does not itself return a delimited string.
Functions such as SYS_CONNECT_BY_PATH can build paths in hierarchical queries, but they are not general-purpose replacements for grouped string aggregation.
How LISTAGG works
The usual grouped form has three important parts: the expression to concatenate, an optional delimiter, and an ordering clause. For example, with an employees table containing department_id and last_name, this query returns one row per department:
SELECT department_id,
LISTAGG(last_name, ', ')
WITHIN GROUP (ORDER BY last_name) AS employee_names
FROM employees
GROUP BY department_id;
Within each department, Oracle sorts the last names as specified and places a comma and space between successive values. The delimiter is not added after the final value. If no delimiter is supplied, the values are concatenated without a separator, which is rarely useful for human-readable output.
The GROUP BY controls which rows are combined. In this example, each department is one group. To make one list for the entire result set, omit the grouping column and use an aggregate query. A filter in WHERE affects which rows enter the aggregation, while a HAVING condition filters groups after aggregation.
Ordering, duplicates, and NULL values
Ordering is part of the result’s meaning, not just presentation. Oracle does not promise a useful order unless the ORDER BY inside WITHIN GROUP specifies one. If two rows have the same sort value, their relative order may be nondeterministic. Add a tie-breaker, such as a unique employee identifier, when a stable result matters:
LISTAGG(last_name, ', ')
WITHIN GROUP (ORDER BY last_name, employee_id)
LISTAGG ignores null measure values. Thus, if last_name is null, that row contributes neither a name nor a delimiter. This can surprise someone expecting a blank field in the list. If nulls need to be represented explicitly, convert them to a chosen label with an expression such as NVL(last_name, '(unknown)'); decide first whether a placeholder is genuinely appropriate for the data.
Duplicates are retained by default. Oracle Database 19c and later support DISTINCT in LISTAGG, for example LISTAGG(DISTINCT job, ', ') WITHIN GROUP (ORDER BY job). Earlier releases require deduplication before aggregation, typically with a subquery or common table expression. Deduplicating can change the meaning of the report, so do it only when repeated values are not significant.
Grouped and analytic examples
A grouped aggregate reduces multiple source rows to one output row for each group. An analytic LISTAGG, by contrast, keeps the source rows and attaches the partition’s concatenated value to each row. For example:
SELECT department_id,
last_name,
LISTAGG(last_name, ', ')
WITHIN GROUP (ORDER BY last_name)
OVER (PARTITION BY department_id) AS department_names
FROM employees;
Each employee row remains in the result, and department_names contains the ordered list for that employee’s department. This is useful when a query needs both detail rows and a group-level text summary. Because the same list is repeated for every row in the partition, it can increase the size of the result substantially; use a grouped query instead if only one row per department is needed.
In both forms, the ordering inside WITHIN GROUP controls the order of values in the concatenated text. It is distinct from an outer query’s ORDER BY, which sorts the rows returned to the client.
Length limits and overflow handling
LISTAGG returns a character string, not an unlimited-size CLOB. Its maximum result length depends on Oracle’s MAX_STRING_SIZE setting and the return type. For a VARCHAR2 result, the documented limit is 4,000 bytes with MAX_STRING_SIZE=STANDARD and 32,767 bytes with MAX_STRING_SIZE=EXTENDED. For RAW, the corresponding limits are 2,000 and 32,767 bytes. These are byte limits, so a multibyte character set may allow fewer characters than the byte figure suggests.
By default, a result that exceeds the limit raises ORA-01489: result of string concatenation is too long. Oracle Database 12.2 and later allow an explicit overflow policy. For example:
LISTAGG(last_name, ', ' ON OVERFLOW TRUNCATE '...' WITH COUNT)
WITHIN GROUP (ORDER BY last_name)
This tells Oracle to return a shortened list with an indicator and a count of omitted values instead of raising the overflow error. WITHOUT COUNT omits that count. The default truncation indicator is an ellipsis, and the default count behavior is WITH COUNT. Truncation does not make the result unlimited; it keeps the result within the applicable return-size limit. Check that the chosen indicator makes sense to the application, and do not mistake a truncated list for a complete one.
When a result must contain every value and may exceed the LISTAGG limit, changing the overflow clause is not a solution: truncation deliberately discards part of the result. Consider returning separate rows, returning a CLOB through an appropriate XML or custom aggregation approach, or using a structured format suited to the client. The right option depends on the Oracle version, required data type, and how the result will be consumed.
When XMLAGG or JSON_ARRAYAGG is a better fit
XMLAGG is an XML aggregation function, not simply a CLOB version of LISTAGG. It aggregates XML nodes and returns XMLType. A common pattern wraps each value in an XML element, orders the elements within XMLAGG, and then serializes or extracts the resulting content. That approach can support larger text output, but element tags, XML character escaping, null handling, and removal of any trailing separator must all be handled deliberately. Test values containing characters such as & and <; XML serialization may escape them, which can affect whether the result is suitable as plain text.
JSON_ARRAYAGG is preferable when the next system expects a JSON array. It preserves the distinction between separate values and avoids ambiguity inherent in a delimited string—for example, a value that itself contains a comma. The JSON output is structured data, however, and should be parsed as JSON rather than treated as a ready-made display string. Review the Oracle version and return type supported by the target environment before relying on a particular JSON aggregation option.
Neither alternative should be selected solely to avoid an error without considering semantics. XML is appropriate for XML workflows, JSON for JSON workflows, and a delimited list for simple display or export cases that have a clear size limit.
Common problems and practical checks
- Missing order: Put the intended sorting rule in
WITHIN GROUP. An outer query sort does not reorder values inside each list. - Unexpected duplicates: Remember that duplicates remain unless removed in the source query or with the 19c-and-later
DISTINCToption. - ORA-01489: Estimate the total bytes of the result, including delimiters. Use truncation only if incomplete output is acceptable; otherwise choose a suitable larger-output design.
- Nulls disappearing: Null measure values are ignored. Use an explicit replacement expression only if a placeholder is desired.
- Ambiguous delimiters: A value may itself contain the chosen delimiter. A plain string then becomes hard to parse reliably. Use escaping rules agreed with the consumer, or return structured JSON instead.
- Different results between runs: Make the ordering deterministic by adding a unique tie-breaker to the ordering expression.
Before putting a list into an application interface or export, confirm whether it is a display value or machine-readable data. A string that looks clear to a person may not be safely reversible into its original values.
How to choose an Oracle aggregation approach
For ordinary, bounded text lists, use LISTAGG and specify a deterministic order. Use LISTAGG(DISTINCT ...) when duplicates truly should be removed and the database is Oracle 19c or later. If the result can exceed the limit, decide whether truncation is acceptable; if it is not, choose a CLOB-capable or structured-output design rather than silently losing data. Use XMLAGG when XML processing is part of the requirement, and JSON_ARRAYAGG when a JSON array is the desired output. For collection processing, consider COLLECT rather than converting values into text prematurely.
Frequently Asked Questions
What is the Oracle equivalent of STRING_AGG?
For a conventional ordered, delimited string, Oracle’s equivalent is LISTAGG. Its ordering is written in a WITHIN GROUP (ORDER BY ...) clause.
Does LISTAGG remove duplicates?
No. It includes duplicates by default. Oracle Database 19c and later support DISTINCT inside LISTAGG; on earlier versions, deduplicate rows in a subquery before aggregating.
How do I prevent ORA-01489 in LISTAGG?
On Oracle Database 12.2 and later, use ON OVERFLOW TRUNCATE if a shortened result is acceptable. If every value must be returned, use a design with an appropriate larger output type rather than truncating.
Does LISTAGG ignore NULL values?
Yes. Null measure values are omitted from the concatenated result. Convert nulls to a placeholder explicitly if the output needs to show them.
Can LISTAGG return a CLOB?LISTAGG returns VARCHAR2 or RAW, depending on its input, not a CLOB. For larger text, investigate XML-based serialization or another CLOB-capable approach and verify its escaping and output behavior.
Related
- Java String split(): Syntax, Regex, and Examples
- Split a String in Excel: Separate Text into Columns
- Accounting Trial Balance Worksheet: How It Works
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula for Grade Average: A Complete GuideThe simplest Excel formula for a grade average is =AVERAGE(range), for example =AVERAGE(B2:B10) when grades are in cells…
- How to Filter Dates by Quarter in ExcelTo filter dates by quarter in Excel, turn on the filter for your date column and choose the…
- How to Randomize Names in Excel With a Simple FormulaTo randomize names in Excel, add a helper column and use a random number for each row. With…
- Excel Formula for Left of CharacterThe simplest Excel formula to extract text to the left of a specific character is =LEFT(A1, FIND("char", A1)…
- VLOOKUP in Excel: Practical Tips for Reliable ResultsFor a reliable VLOOKUP in Excel, put the value you want to find in the first column of…
- Unable to Put a Filter in Excel? Common FixesIf you are unable to put a filter in Excel, select a cell in your data and choose…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.