FB

Oracle LISTAGG Function with ExamplesThis 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.

Get Your Free PDF: 9 Ways to Improve your Database Skills

Oracle SQL Function Cheat Sheet

Subscribe and get a PDF cheat sheet of all of the Oracle SQL functions and parameters for easy reference!

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit

String Aggregation Example

Let’s use our sample data set that we’ve using 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.

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.

 

Similar Functions

There aren’t really any functions which 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.

Get Your Free PDF: 9 Ways to Improve your Database Skills

Oracle SQL Function Cheat Sheet

Subscribe and get a PDF cheat sheet of all of the Oracle SQL functions and parameters for easy reference!

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit

Image courtesy of David Castillo Dominici / FreeDigitalPhotos.net

Oracle SQL Function Cheat Sheet

Subscribe and get a PDF cheat sheet of all of the Oracle SQL functions and parameters for easy reference!

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit

Get Your FREE PDF: 9 Ways to Improve your Database Skills