Oracle LISTAGG Function Example

Today I read about LISTAGG aggregate / analytic function in Oracle, and thought would write a short blog about this, explaining various methods of using LISTAGG with suitable examples. I’m going to approach this tutorial with really simple examples so that you can understand LISTAGG completely. The tutorial assumes you know some basics of SQL, using aggregate functions and analytic functions. You don’t need any understanding on LISTAGG.  The different steps involved in this example are summarized below:

Step by Step Approach to Learn LISTAGG SQL Function

1. LISTAGG Introduction

In Oracle documentation terms, LISTAGG for a specified measure, orders data within each group specified by ORDER BY, and then concatenates the values of the measure column. !!! Sounds Greek and Latin? Don’t worry, we will slowly break this definition into examples that you can understand very easily.  At this stage, you should be knowing that LISTAGG helps you to combine values across columns, with a specified delimiter / condition and allows some ordering options in the process. This much of definition would be helpful for you to get started with LISTAGG. For a detailed syntax diagram of LISTAGG for Oracle nerds, you can refer this link. The syntax of LISTAGG is provided below:

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

We will now approach this syntax with a set of examples, and you will understand this in no time.

2.Simple LISTAGG Example

We now create a simple students table with grade and student name as columns. We will also pump in some test data for the basic LISTAGG example.

CREATE TABLE STUDENTS
(
GRADE VARCHAR2(20),
ST_NAME VARCHAR2(50)
)

INSERT INTO STUDENTS VALUES ('A','JOHN')
INSERT INTO STUDENTS VALUES ('A','JOE')
INSERT INTO STUDENTS VALUES ('A','JOAKIM')
INSERT INTO STUDENTS VALUES ('B','BROAD')
INSERT INTO STUDENTS VALUES ('C','KEVIN')
INSERT INTO STUDENTS VALUES ('C','SEEHO')
INSERT INTO STUDENTS VALUES ('D','AIDAN')

SELECT * FROM STUDENTS

A screen dump of the table is shown below:

LISTAGG- Simple Example - Input Table Screenshot

LISTAGG SQL example to separate all names (measure) that belong to Grade A, through a “,” (delimiter) is shown below:

SELECT LISTAGG(ST_NAME,',') WITHIN GROUP(ORDER BY ST_NAME DESC) FROM STUDENTS WHERE GRADE='A'

As expected, the output is a single row of data, as shown below

JOHN,JOE,JOAKIM

Congratulations, you have written your first LISTAGG example. If you note the SQL above, we have sorted the names in descending order by using DESC keyword. The default is “Ascending” order.  How can you get the result grouped by grades? To do this, you include a GRADE column in your SELECT clause, and add a GROUP BY to the end. This is shown below:

SELECT GRADE,LISTAGG(ST_NAME,',') WITHIN GROUP(ORDER BY ST_NAME) FROM STUDENTS GROUP BY GRADE

The output is shown in the screenshot below:

LISTAGG - Group set aggregate - example output

We achieved the grouping of names at a GRADE level, by using GROUP BY clause in the SQL. This explains the usage of LISTAGG as a GROUP SET AGGREGATE function. The LISTAGG function has returned the result for every group defined in the GROUP BY clause. We will now see some advanced examples of LISTAGG.

3. Combining columns in LISTAGG Output

It is possible to combine more than one column in the LISTAGG output. To illustrate this, we will add one more column to the table and update it with some data as shown below:

/* Add a new column to the table */
ALTER TABLE STUDENTS ADD (ST_LAST_NAME VARCHAR2(50))
/* set the column value to M across all rows */
UPDATE STUDENTS SET ST_LAST_NAME='M'

To combine ST_NAME and ST_LAST_NAME as an example, you run a LISTAGG SQL as shown below:

SELECT GRADE,LISTAGG(ST_NAME||'-'||ST_LAST_NAME,';') WITHIN GROUP(ORDER BY ST_NAME DESC) FROM STUDENTS GROUP BY GRADE

The output is shown below:

Combining more than one column in LISTAGG - Example Output

As you can see, we have combined both the “name” columns with a ‘;’ separator, grouped by GRADE.

4. Handling NULLS in LISTAGG

You can control if columns with NULL values should appear first or last, by using NULLS FIRST / NULLS LAST inside the ORDER BY clause. Default value is NULLS LAST. To explain this, we will insert a row into the table with a NULL value on last name as shown below:

INSERT INTO STUDENTS VALUES('A','MURRAY',NULL)

Here are two SQL statements that handle NULLS differently in LISTAGG. The output is presented side by side for a comparison.

SELECT 'nulls first',GRADE,LISTAGG(ST_NAME||'-'||ST_LAST_NAME,';') WITHIN GROUP(ORDER BY ST_LAST_NAME NULLS FIRST ) FROM STUDENTS WHERE GRADE='A' GROUP BY GRADE
union all
SELECT 'nulls last',GRADE,LISTAGG(ST_NAME||'-'||ST_LAST_NAME,';') WITHIN GROUP(ORDER BY ST_LAST_NAME ) FROM STUDENTS WHERE GRADE='A' GROUP BY GRADE

Note: I have given a UNION ALL to get the output in a single window so that I can grab a screenshot. This is for illustrative purposes only.

Handling NULLS in LISTAGG Usage

As you can see from above, we got MURRAY as first data as we wanted NULLS to come first in the output. It is just the opposite in the second row. 

5. LISTAGG as Reporting Aggregate

We will now discuss how to use LISTAGG as an analytic function in Oracle. To explain this, we will add a SCORE column to the students table and update it with some random number using DBMS_RANDOM package. We will also add a SUB_GRADE column to illustrate the usage better. So, be ready to modify your test table a bit now and use the SQLs below for that:

ALTER TABLE STUDENTS ADD(SCORE NUMBER)

UPDATE STUDENTS SET SCORE=TRUNC(DBMS_RANDOM.VALUE(1,100))

ALTER TABLE STUDENTS ADD (SUB_GRADE VARCHAR2(10))

UPDATE STUDENTS SET SUB_GRADE='A1' WHERE GRADE='A' AND ROWNUM <3 AND SUB_GRADE IS NULL
UPDATE STUDENTS SET SUB_GRADE='A2' WHERE GRADE='A' AND ROWNUM <3 AND SUB_GRADE IS NULL
UPDATE STUDENTS SET SUB_GRADE='B1' WHERE GRADE='B' AND ROWNUM <3 AND SUB_GRADE IS NULL
UPDATE STUDENTS SET SUB_GRADE='C1' WHERE GRADE='C' AND ROWNUM <3 AND SUB_GRADE IS NULL
UPDATE STUDENTS SET SUB_GRADE='D1' WHERE GRADE='D' AND ROWNUM <3 AND SUB_GRADE IS NULL

Here is a screen dump of the modified table to test analytic examples on LISTAGG:

LISTAGG As Analytic Function - Setup Table

Now, if you want to know the MIN score at a Grade and Sub grade level, you run a query as shown below:

SELECT GRADE,SUB_GRADE,MIN(SCORE) FROM STUDENTS GROUP BY GRADE,SUB_GRADE

Now, if you want to LISTAGG all MIN Scores at a GRADE and sub grade level, you use LISTAGG with a PARTITION class, that partitions on Grade.  An example SQL is shown below:

SELECT GRADE,
       SUB_GRADE,
       MIN(SCORE),
       LISTAGG(MIN(SCORE), ',') WITHIN GROUP(ORDER BY GRADE) OVER(PARTITION BY GRADE)S
  FROM STUDENTS
 GROUP BY GRADE, SUB_GRADE

This query produces an output as shown below: (as we are using DBMS_RANDOM earlier, you may get different results for this query )

LISTAGG - Analytic Function Output

You can try different examples of using analytic functions with LISTAGG on the same lines and post your queries if any.

6.LISTAGG without Delimiter

As per the syntax of LISTAGG, the delimiter is optional and is defaulted to NULL if you don’t specify it. So, the SQL

SELECT GRADE,
       SUB_GRADE,
       MIN(SCORE),
       LISTAGG(MIN(SCORE)) WITHIN GROUP(ORDER BY GRADE) OVER(PARTITION BY GRADE)S
  FROM STUDENTS
 GROUP BY GRADE, SUB_GRADE

Returns the same output as in (5), but there will not be a delimiter separating 24 and 28 (column 4 in the output). It would rather be a NULL delimited output as shown below:

LISTAGG- NULL / NO Delimiter - Example Output

7.Using Columns as Delimiters in LISTAGG

You can use another column name as a delimiter in LISTAGG. However, the column name should be  a part of the SQL query, otherwise you would get an error. For example, if you run this SQL;

SELECT GRADE,LISTAGG(ST_NAME,SUB_GRADE) WITHIN GROUP(ORDER BY ST_NAME DESC) FROM STUDENTS GROUP BY GRADE
---ORA-30497: Argument should be a constant or a function of expressions in GROUP BY

You get the ORA error as shown above, as the column SUB_GRADE is not in the select list. Replacing this with GRADE would get rid of ORA 30497 error, and will give an output that uses the value in GRADE column as a delimiter. However, it is a bad idea to use a column value as a delimiter unless you have an absolute requirement for this.(sometimes it affects the readability of your output)

8.Using PL SQL Function as Delimiter in LISTAGG

You can use a simple PL SQL function as a delimiter in LISTAGG. As an example, consider the function below:

create or replace function f(grade in varchar2)  
RETURN VARCHAR2 IS
delimiter varchar2(2);
BEGIN
if grade='A' then
  delimiter:=':';
else
  delimiter:=';';
end IF;
return delimiter;

end f;

This function returns the delimiter as ‘:’ if the GRADE is A. It returns ‘;’ in all other cases. You can invoke this function via SQL in LISTAGG delimiter clause. This is shown below:

SELECT GRADE,LISTAGG(ST_NAME,f(GRADE)) WITHIN GROUP(ORDER BY ST_NAME DESC) FROM STUDENTS GROUP BY GRADE

The output of this SQL is shown below:

Using a function as delimiter in LISTAGG

As you can infer from the output, Row with Grade A has ‘:’ as delimiter. However, Row # C has ‘;’ as delimiter.

That completes a breezy tutorial on Oracle LISTAGG function. Hope the examples we have provided helped you to understand LISTAGG functionality, and you are now geared to use this function in your day today requirements powerfully. You can post any questions that you have around LISTAGG in the comments section of this blog. Happy LISTAGGing.