Skip to content
The Chat Archive
qwen3-30b-a3b· Alibaba (Qwen) LMArena Human Preference 140K

what is the sql syntax for listagg in oracle

  1. User

    what is the sql syntax for listagg in oracle

  2. Assistant

    In Oracle Database, the LISTAGG function 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 a GROUP BY clause).

    Example

    Suppose you have a table employees with columns department_id and name:

    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 OVERFLOW clause 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 BY to ensure consistent results.
    • Version Requirement: LISTAGG is not available in Oracle 11g or earlier. For older versions, use XMLAGG or WM_CONCAT (deprecated).
    • Limitations: The default maximum length is 4000 bytes. Use ON OVERFLOW to avoid errors.

    Let me know if you need help with alternatives for older Oracle versions!

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.