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:
LISTAGGis the clearest option for a short, human-readable delimited list, with explicit ordering and, on supported releases, duplicate removal or truncation.JSON_ARRAYAGGsuits application interfaces that expect JSON and need values kept as separate elements.XMLAGGcan 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
DISTINCTwhere supported or deduplicate the input rows. - Do not assume a long result automatically becomes a CLOB. Standard
LISTAGGreturns a bounded character result and can raise an overflow error. - Do not use undocumented
WM_CONCATin 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
- C++ Split String Into Array: Practical Methods
- JavaScript split(): Syntax, Examples, and Common Mistakes
- Chemistry Balance Equation Worksheet: Practice and Answers
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Hide a Column If a Cell Is Blank in ExcelYes—but Excel has no built-in setting that automatically hides a column when a cell becomes blank. In Excel…
- SQL Split String by Delimiter: Functions by DatabaseThere is no single SQL function for splitting a string by a delimiter across all databases. Use STRING_SPLIT…
- Checklist Examples in Excel: Templates and FormulasUseful checklist examples in Excel include task lists, inspections, inventory counts, event plans, and habit trackers. For a…
- Excel FILTER Function: Return Results Without DuplicatesExcel’s FILTER function does not remove duplicates by itself. To return matching results without repeats, wrap FILTER inside…
- How to Combine Names in Excel With a Formula EasilyTo combine first, middle, and last names in Excel, put them in separate columns and enter =TRIM(A2&" "&B2&"…
- Excel XLOOKUP Between Two Worksheets: ExamplesIf your lookup value is on one worksheet and the data to search is on another, XLOOKUP can…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.