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.

SELECT country, last_name
FROM customers;

COUNTRY	LAST_NAME
USA	Smith
USA	Jones
Canada	Brown
UK	Allan
USA	Cooper
(null)	Thompson
France	Manson
(null)	(null)

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

COUNTRY	LISTAGG_OUTPUT
Canada	Brown
France	Manson
UK	Allan
USA	Cooper, Jones, Smith
(null)	Thompson

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:

LISTAGG ( 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.

SELECT LISTAGG(column1, ',') WITHIN GROUP (ORDER BY column2) as listagg_output
FROM (
  SELECT DISTINCT column1, column2
  FROM test_table
);

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

SELECT col1, LISTAGG(col2, ',') WITHIN GROUP (ORDER BY col2)
FROM (
  SELECT  col1,
  col2,
  row_number() OVER(PARTITION BY col1, col2 ORDER BY col1) as rn
  FROM test_table
  ORDER BY col1 ,col2
)
WHERE rn = 1
GROUP 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.

SELECT * FROM customers;

 

FIRST_NAME	LAST_NAME	COUNTRY	FULL_ADDRESS	EMPLOYEES	START_DATE
John	Smith	USA	10 Long Road	4	12/04/2010
Sally	Jones	USA	50 Market Street	10	04/07/2011
Steve	Brown	Canada	201 Flinders Lane	15	21/03/2009
Mark	Allan	UK	8 Smith Street	23	01/02/2001
Adam	Cooper	USA	14 Wellington Road	55	(null)
Josh	Thompson	(null)	80 Victoria Street	1	10/02/2012
Peter	Manson	France	5 Johnson St	(null)	16/10/2012
(null)	(null)	(null)	155 Long Road	(null)	16/10/2012

Example 1

This example uses LISTAGG to aggregate all values in the table

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

Result:

LISTAGG_OUTPUT

Allan,Brown,Cooper,Jones,Manson,Smith,Thompson

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.

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

LISTAGG_OUTPUT

Cooper,Jones,Smith

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.

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

 

COUNTRY	LISTAGG_OUTPUT
Canada	Brown
France	Manson
UK	Allan
USA	Cooper,Jones,Smith
(null)	Thompson

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.

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

Result:

ORA-30491: missing ORDER BY clause
30491. 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.

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

Result:

COUNTRY	LISTAGG_OUTPUT
Canada	Brown
France	Manson
UK	Allan
USA	CooperJonesSmith
(null)	Thompson

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.

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

COUNTRY	LISTAGG_OUTPUT
Canada	Brown
France	Manson
UK	Allan
USA	Cooper, Jones, Smith
(null)	Thompson

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.

SELECT
first_name,
last_name,
country,
LISTAGG(last_name, ',')
  WITHIN GROUP (ORDER BY last_name)
  OVER (PARTITION BY country) AS LISTAGG_OUTPUT
FROM customers;

FIRST_NAME	LAST_NAME	COUNTRY	LISTAGG_OUTPUT
Steve	Brown	Canada	Brown
Peter	Manson	France	Manson
Mark	Allan	UK	Allan
Adam	Cooper	USA	Cooper,Jones,Smith
Sally	Jones	USA	Cooper,Jones,Smith
John	Smith	USA	Cooper,Jones,Smith
Josh	Thompson	(null)	Thompson
(null)	(null)	(null)	Thompson

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.

SELECT
first_name,
last_name,
country,
LISTAGG(last_name, ',')
  WITHIN GROUP (ORDER BY last_name)
  OVER (PARTITION BY last_name) AS LISTAGG_OUTPUT
FROM customers

FIRST_NAME	LAST_NAME	COUNTRY	LISTAGG_OUTPUT
Mark	Allan	UK	Allan
Steve	Brown	Canada	Brown
Adam	Cooper	USA	Cooper
Sally	Jones	USA	Jones
Peter	Manson	France	Manson
John	Smith	USA	Smith
Josh	Thompson	(null)	Thompson
(null)	(null)	(null)	(null)

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?

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

Result:

ORA-00937: not a single-group group function
00937. 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: