Oracle LISTAGG Function

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.

Typically, you use the LISTAGG() function 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’ll 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. Additionally, 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:

• The column_name can be a column, constant, or expression that involves the columns.
• 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 by default.
• 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)

Returning 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.

Here’s the syntax of the LISTAGG function with the ON OVERFLOW ERROR option:

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.

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)

Truncating 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)

Summary #

• Use the LISTAGG() function to transform data from multiple rows into a list of values separated by a specified delimiter.