qwen3-30b-a3b· Alibaba (Qwen) LMArena Human Preference 140K
what is the sql syntax for listagg in oracle
- User
what is the sql syntax for listagg in oracle
- Assistant
In Oracle Database, the
LISTAGGfunction is used to concatenate values from multiple rows into a single string. It is available in Oracle 12c and later. Here's the syntax and explanation:
Basic Syntax
LISTAGG(expression, delimiter) WITHIN GROUP (ORDER BY order_expression)expression: The column or expression whose values you want to concatenate.delimiter: The string used to separate the concatenated values (e.g.,', ','; ',' | ').ORDER BY: Specifies the order of the concatenated values. Required for predictable results.WITHIN GROUP: Indicates that the aggregation is performed within the group (e.g., in aGROUP BYclause).
Example
Suppose you have a table
employeeswith columnsdepartment_idandname:SELECT department_id, LISTAGG(name, ', ') WITHIN GROUP (ORDER BY name) AS employees FROM employees GROUP BY department_id;Output:
DEPARTMENT_ID | EMPLOYEES --------------|------------------- 10 | Alice, Bob, Charlie 20 | David, Emma
Optional: Handling Overflow
If the concatenated string exceeds the maximum length (default is 4000 bytes), Oracle throws an error. Use the
ON OVERFLOWclause to handle this:LISTAGG(expression, delimiter) WITHIN GROUP (ORDER BY order_expression) ON OVERFLOW TRUNCATE [string] [WITH COUNT]TRUNCATE: Truncates the result instead of throwing an error.[string]: A suffix to indicate truncation (e.g.,'...').WITH COUNT: Appends the number of truncated values.
Example:
SELECT department_id, LISTAGG(name, ', ') WITHIN GROUP (ORDER BY name) ON OVERFLOW TRUNCATE '...' WITH COUNT AS employees FROM employees GROUP BY department_id;
Key Notes
- Order Matters: Always use
ORDER BYto ensure consistent results. - Version Requirement:
LISTAGGis not available in Oracle 11g or earlier. For older versions, useXMLAGGorWM_CONCAT(deprecated). - Limitations: The default maximum length is 4000 bytes. Use
ON OVERFLOWto avoid errors.
Let me know if you need help with alternatives for older Oracle versions!
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.