Oracle LISTAGG Function Usage, Tips & Examples

This article explains the Oracle LISTAGG functions, which is one of the more difficult functions to understand.

Purpose of the Oracle LISTAGG Function

The purpose of the Oracle LISTAGG function is to allow you to perform aggregation of strings from data in columns.

What is string aggregation? Well, it's a technique similar to concatenation, but it allows you to aggregate data in a GROUP BY, combining strings that have matches in a value in another column.

It's a new function in Oracle 11g R2, so you may not have heard of it if you're using an older version. Actually, you might not have heard of it even if you're using 11g R2!

This function might be easier to explain with an example.

While you're here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here:

String Aggregation Example

Let's use our sample data set that we've used in our other Oracle SQL function articles.

1SELECT country, last_name
2FROM customers;

Using a LISTAGG function, we could have an output like this:

As you can see, the last_name values have been concatenated together and split by a comma, which are grouped by each different value of country.

Let's learn how we can use this function to get this result.

LISTAGG Syntax

The LISTAGG syntax is:

1LISTAGG ( measure_expr [, delimiter]) WITHIN GROUP (order_by_clause) [OVER query_partition_clause]

Let's have a look at the parameters of this function in more detail.

LISTAGG Parameters

There are several mandatory and optional parameters in the LISTAGG function. The parameters of the LISTAGG function are:

• measure_expr (mandatory): This is the column (or expression) that you wish to concatenate the values of. In the example above, I used last_name.
• delimiter (optional): This is the delimiter, or the character in between each value of measure_expr. If this is not specified, no value is used.
• order_by_clause (mandatory): This is the order of the concatenated values in measure_expr are listed.
• query_partition_clause (optional): This allows you to use LISTAGG as an analytic function, which we will see in the examples section below.

Usage of the LISTAGG Function

There are a few ways you can use this function.

If you use it without any grouping, LISTAGG operates on all rows and returns a single row.

If you use it with grouping, LISTAGG operates on and returns a row for each group defined by the GROUP BY clause.

If you use it as an analytic function, LISTAGG partitions the query result set into groups, based on one or more expressions in the query_partition_clause.

We can see how this works in the examples below.

Can You Run Oracle LISTAGG With DISTINCT Results?

Yes, you can run a query using LISTAGG in Oracle and eliminate duplicates from the output of the LISTAGG function.

The way to do this is to SELECT from a subquery which already removes the duplicates, and do your LISTAGG on that.

1SELECT LISTAGG(column1, ',') WITHIN GROUP (ORDER BY column2) as listagg_output
2FROM (
3  SELECT DISTINCT column1, column2
4  FROM test_table
5);

There is a more advanced version, which has been documented on Stack Overflow here, which can be used for more columns.

 1SELECT col1, LISTAGG(col2, ',') WITHIN GROUP (ORDER BY col2)
 2FROM (
 3  SELECT  col1,
 4  col2,
 5  row_number() OVER(PARTITION BY col1, col2 ORDER BY col1) as rn
 6  FROM test_table
 7  ORDER BY col1 ,col2
 8)
 9WHERE rn = 1
10GROUP BY col1;

Just substitute your tables and columns into this query as needed.

What Is The Oracle LISTAGG LIMIT?

The limit of the output of the Oracle LISTAGG function is 4000 bytes. Any more than this and you'll receive an Oracle error.

You can build a custom function to get the output of LISTAGG from multiple rows and combine them into a CLOB value if that's what you really need, but that's outside the scope of this article.

Is There An Oracle LISTAGG Alternative?

Yes, there are a couple of alternatives

The first is the WM_CONCAT built-in function. This is not supported by Oracle any more and is not documented, so I suggest avoiding it.

Alternatively, you can create a generic user-defined aggregate function, that does the work for you, in PL/SQL. This has been detailed in an AskTom article here.

You can also write a specific user-defined function for your purpose. If you're only doing it for one query or one section of your application, you can write a specific function which gets you the result you need, and isn't used by other areas.

Does Oracle LISTAGG Work on Multiple Columns?

Do you want to be able to use multiple columns inside your LISTAGG function and output those as values?

Well, you can do that using LISTAGG.

You'll just need to concatenate them inside the LISTAGG function. You can use either the CONCAT function or the doubple pipe character (||). As I mentioned in the article on CONCAT, both of these methods have the same performance.

Examples of the Oracle LISTAGG Function

Here are some examples of the LISTAGG function. I find that examples are the best way for me to learn about code, even with the explanation above.

Before we see some examples, let's look at the sample data that we'll use. This will make it easier to explain the examples.

You can download the Create Table and Insert scripts from my GitHub repository here.

1SELECT * FROM customers;

Example 1

This example uses LISTAGG to aggregate all values in the table

1SELECT LISTAGG(last_name, ',') WITHIN GROUP (ORDER BY last_name) as LISTAGG_OUTPUT
2FROM customers;

Result:

It shows all values of last_name in the table, separated by a single comma character.

Example 2

This example shows you how to use LISTAGG on a subset of records using the WHERE clause.

1SELECT LISTAGG(last_name, ',') WITHIN GROUP (ORDER BY last_name) as LISTAGG_OUTPUT
2FROM customers
3WHERE country = 'USA';

As you can see, it shows a single row, but only those last_names where the country is USA.

Example 3

This example uses LISTAGG in Oracle to aggregate the last_name values for each country.

Because LISTAGG is an aggregate function, any other columns listed here need to have a GROUP BY. It can also use the PARTITION BY, but we'll cover that later.

1SELECT country, LISTAGG(last_name, ',') WITHIN GROUP (ORDER BY last_name) as LISTAGG_OUTPUT
2FROM customers
3GROUP BY country;

Each country is listed, along with the last_name only for the records that match the country.

Example 4

This example shows what happens if we don't specify the ORDER BY.

1SELECT country, LISTAGG(last_name, ',') WITHIN GROUP (last_name) as LISTAGG_OUTPUT
2FROM customers
3GROUP BY country;

Result:

1ORA-30491: missing ORDER BY clause
230491. 00000 -  "missing ORDER BY clause"

You'll get this error if you leave out the words ORDER BY, or the entire clause that's inside the brackets.

Example 5

This example shows what happens using the default delimiter, using the example above for each country.

1SELECT country, LISTAGG(last_name) WITHIN GROUP (ORDER BY last_name) as LISTAGG_OUTPUT
2FROM customers
3GROUP BY country;

Result:

The result is the same, but the data is not separated by a comma. The default delimiter is nothing, so there is nothing in between each value.

Example 6

This is the same query as above, but using two characters as a delimiter.

1SELECT country, LISTAGG(last_name, ', ') WITHIN GROUP (ORDER BY last_name) as LISTAGG_OUTPUT
2FROM customers
3GROUP BY country;

This result is a little more readable, as we have a comma and a space in between the values.

Example 7

This example shows how the PARTITION BY is used. I've added a few extra columns to make more sense of it.

1SELECT
2first_name,
3last_name,
4country,
5LISTAGG(last_name, ',')
6  WITHIN GROUP (ORDER BY last_name)
7  OVER (PARTITION BY country) AS LISTAGG_OUTPUT
8FROM customers;

Each row from the customers table is shown. For each row, we have the LISTAGG function, which shows an aggregation of last_name values.

Notice how we did not use a GROUP BY. The GROUP BY is not needed if you use the OVER PARTITION BY clause.

The PARTITION BY clause tells you how the values inside the LISTAGG are split. You can see that the last_name values that are shown are those for the country, and in the case of records with a country of USA, they are repeated.

Example 8

This example is the same as above, but the PARTITION BY clause is on the last_name field.

1SELECT
2first_name,
3last_name,
4country,
5LISTAGG(last_name, ',')
6  WITHIN GROUP (ORDER BY last_name)
7  OVER (PARTITION BY last_name) AS LISTAGG_OUTPUT
8FROM customers

As you can see, the LISTAGG function doesn't really do much. This is because it's partitioned on the same value that is being aggregated.

Example 9

What if we didn't want a partition and wanted to show the other data as well?

1SELECT first_name, last_name, country,
2LISTAGG(last_name, ',') WITHIN GROUP (ORDER BY last_name) as LISTAGG_OUTPUT
3FROM customers

Result:

1ORA-00937: not a single-group group function
200937. 00000 -  "not a single-group group function"

Oracle treats this as using an aggregate function, and as a result, you need to use a GROUP BY.

You can read more about the ORA-00937 error here.

Similar Functions

There aren't really any functions that are similar to the LISTAGG function. However, one that I mentioned was:

• CONCAT - Allows string values to be joined or concatenated together.

You can find a full list of Oracle functions here.

So, that's how you use LISTAGG in Oracle.

While you're here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here: