Oracle LISTAGG

Summary: in this tutorial, you will learn how to use the Oracle LISTAGG() function to transform data from mutliple 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-seprated values or other human readable format for the reporting purpose.

For the demonstration 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.

employees table
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)
Oracle LISTAGG - Employees Job Title Example

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:

products table
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 Example

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 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 mutliple rows into a list of values separated by a specified delimiter.

Was this tutorial helpful?