Oracle LISTAGG

Summary: in this tutorial, you will learn how to use the Oracle LISTAGG() function to transform data from multiple rows into a single list of values separated by a specified delimiter.

The Oracle LISTAGG() function is an aggregation function that transforms data from multiple rows into a single list of values separated by a specified delimiter. The Oracle LISTAGG() function is typically used to denormalize values from multiple rows into a single value which can be a list of comma-separated values or other human readable format for the reporting purpose.

For the demonstration of the functionality of the LISTAGG() function, we will use the employees and products tables from the sample database.

Basic usage of Oracle LISTAGG() function

Sometimes, you may want to aggregate data from a number of rows into a single row and associate the result row with a specific value.

For example, the following query returns a comma-separated list of employees for each job title.

SELECT
    job_title,
    LISTAGG(
        first_name,
        ','
    ) WITHIN GROUP(
    ORDER BY
        first_name
    ) AS employees
FROM
    employees
GROUP BY
    job_title
ORDER BY
    job_title;
Code language: SQL (Structured Query Language) (sql)

In this example, the LISTAGG() function concatenates the first names of employees who have the same job title. In addition, it sorts the first names in ascending order before performing the aggregation.

The following illustrates the syntax of the Oracle LISTAGG() function:

LISTAGG (
    [ALL] column_name [,
    delimiter]
) WITHIN GROUP(
    ORDER BY
        sort_expressions
);
Code language: SQL (Structured Query Language) (sql)

In this syntax:

1. The column_name can be a column, constant, or expression that involves the columns.
2. The delimiter is a string that separates the values in the result row. The delimiter can be NULL , a string literal, bind variable, or constant expression. If you omit the delimiter, the function uses a NULL value by default.
3. The sort_expressions is a list of sort expressions to sort data in ascending (ASC) or descending (DESC) order. Note that you can use NULLS FIRST or NULLS LAST in the sort_expression to control the sort order of NULLs. By default, the LISTAGG() function uses ASCENDING and NULLS LAST options.

For example, the following query retrieves a list of order ids with their corresponding products in the comma-separated values format:

SELECT
    order_id,
    LISTAGG(
        product_name,
        ';'
    ) WITHIN GROUP(
    ORDER BY
        product_name
    ) AS products
FROM
    order_items
INNER JOIN products
        USING(product_id)
GROUP BY
    order_id;
Code language: SQL (Structured Query Language) (sql)

Oracle LISTAGG(): return an error on overflow

If the result row exceeds the maximum length of the supported data type, you can either return an error or truncate the result row and concatenate a truncation literal.

The following shows the syntax:

LISTAGG(
    [ALL] column_name 
    [, delimiter] ON OVERFLOW ERROR
) WITHIN GROUP(
    ORDER BY
        sort_expression
);
Code language: SQL (Structured Query Language) (sql)

Note that the LISTAGG() function returns an error by default.

For example, the following statement retrieves product categories (category_id) and their corresponding product descriptions:

SELECT
    category_id,
    LISTAGG(
        description,
        ';' ON OVERFLOW ERROR
    ) WITHIN GROUP(
    ORDER BY
        description
    ) AS products
FROM
    products
GROUP BY
    category_id
ORDER BY
    category_id;
Code language: SQL (Structured Query Language) (sql)

Oracle issued the following error because the result string is too long:

ORA-01489: result of string concatenation is too long 
Code language: SQL (Structured Query Language) (sql)

Oracle LISTAGG(): truncate result on overflow

Since Oracle 12c Release 2, you can use the ON OVERFLOW TRUNCATE clause to handle the overflow error gracefully. The following illustrates the syntax:

LISTAGG(
        [ALL] column_name 
        [, delimiter] ON OVERFLOW TRUNCATE
    ) WITHIN GROUP(
        ORDER BY
            sort_expression
    );
Code language: SQL (Structured Query Language) (sql)

By default, LISTAGG() function uses an ellipsis (…) and the number of overflow characters such as …(120).

The following example shows the category id list and their corresponding product descriptions which are truncated:

SELECT
    category_id,
    LISTAGG(
        description,
        ';' ON OVERFLOW TRUNCATE
    ) WITHIN GROUP(
    ORDER BY
        description
    ) AS products
FROM
    products
GROUP BY
    category_id
ORDER BY
    category_id;
Code language: SQL (Structured Query Language) (sql)

If you don’t want to use the default ellipsis, you can specify a custom truncation literal by defining it after the ON OVERFLOW TRUNCATE clause as follows:

 SELECT
    category_id,
    LISTAGG(
        description,
        ';' ON OVERFLOW TRUNCATE '!!!'
    ) WITHIN GROUP(
    ORDER BY
        description
    ) AS products
FROM
    products
GROUP BY
    category_id
ORDER BY
    category_id;Code language: SQL (Structured Query Language) (sql)

To remove the overflow character count, you use the WITHOUT COUNT clause. Note that the LISTAGG() function uses the WITH COUNT clause by default. See the following example:

SELECT
    category_id,
    LISTAGG(
        description,
        ';' ON OVERFLOW TRUNCATE '!!!' WITHOUT COUNT
    ) WITHIN GROUP(
    ORDER BY
        description
    ) AS products
FROM
    products
GROUP BY
    category_id
ORDER BY
    category_id;
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the Oracle LISTAGG() function to transform data from multiple rows into a list of values separated by a specified delimiter.