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:
Is it possible to have more than one LISTAGG pulling from the same table?
I have a course registration table (for a college) and wanted to have three LISTAGGs, one for three different courses. Each would list the grades and semester each time that student took that course.
Yes, it is possible to have multiple LISTAGG functions on the same table. I’m not sure what your table structure is, but I found an example on StackOverflow that might be relevant for you:
http://stackoverflow.com/questions/17639655/2-listagg-in-one-sql-select-in-oracle
Hope it helps!
hi Is there any way we can make the column value like below if does not have data ?
Cooper, ,Smith
I have 3 tables joined and based on a key I have to listagg all the values, but this causes a repitiion of data as listagg is done on each table. Something like ID, NAME, COURSE
1 | 3,3,3| 12,34,43
Hi
Is it possible to include the LISTAGG in a view – so I have a view that pulls togather informataion from various different tables – and I want to populate one of the columns in the view with concatanated values . I dont wnat to change the rest of the view just add this new field – how do I insert the LISTAGG function into the body of the view ?
Hi Seodra,
Yes, you should be able to use LISTAGG in a view like any other function.
You can use the ALTER VIEW statement to do this. First you would need to get the SQL used to create the view, which can be found in your source code or inside the database. Then you can use the ALTER VIEW to add the extra column for the LISTAGG function. to the view.
Hope it helps.
Ben
I have 10000 rows in table with userid column.
I need each row with 1000 userids with comma(,) like below
row 1: userid1,userid2,userid3,…,userid1000
row 2: userid1001,userid1002,userid1003,…,userid2000
row 3: userid2001,userid2002,userid2003,…,userid3000
is there any way we can achieve this LISTAGG.
I need to create a listagg that will have work q and the task assigned all grouped by an id column and look like below
Table
ID WORKQ TASK
1 A T1
1 A T2
1 B T3
1 B T4
OUTPUT
1 LINE = A (T1,T2) B(T3,T4) Including parentheses , But I do not want to show the id column
I have created a query using listagg to get all the clubs a person was in while at the university. The table I am trying to update into is limited to 255 characters and some of my rows have more characters than others how do I use substring in this query to truncate the column.