Summary: in this tutorial, you will learn how to use the OracleLISTAGG()
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 employeesFROM employeesGROUP BY job_titleORDER 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:
- 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. Thedelimiter
can be NULL , a string literal, bind variable, or constant expression. If you omit the delimiter, the function uses a NULL value 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 useNULLS FIRST
orNULLS LAST
in thesort_expression
to control the sort order ofNULLs
. By default, theLISTAGG()
function usesASCENDING
andNULLS 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 productsFROM order_itemsINNER 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 productsFROM productsGROUP BY category_idORDER 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 productsFROM productsGROUP BY category_idORDER 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 productsFROM productsGROUP BY category_idORDER 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 productsFROM productsGROUP BY category_idORDER BY category_id;
Code language: SQL (Structured Query Language) (sql)
In this tutorial, you have learned how to use the Oracle LISTAGG()
function totransform data from multiple rows into a list of values separated by a specified delimiter.
Was this tutorial helpful?