Oracle / PLSQL: LISTAGG Function

Isaac
Posted by By Isaac 1 year Ago 2 Min Read

This article is written about how to use the Oracle/PLSQL LISTAGG function with syntax and examples.

Description

The Oracle/PLSQL LISTAGG function concatenates values of the measure_column for each GROUP based on the order_by_clause.

Syntax

The syntax for the LISTAGG function in Oracle/PLSQL is:

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

Parameters or Arguments

measure_column

The column or expression whose values you wish to concatenate together in the result set. Null values in the measure_column are ignored.

delimiter

Optional. It is the delimiter to use when separating the measure_column values when outputting the results.

order_by_clause

It determines the order that the concatenated values (ie: measure_column) are returned.

Returns

The LISTAGG function returns a string value.

Applies To

The LISTAGG function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g Release 2

Example

The LISTAGG function can be used in Oracle/PLSQL.

Since this is a more complicated function to understand, let’s look at an example that includes data to demonstrate what the function outputs.

If you had a products table with the following data:

product_id product_name

1001 Bananas

1002 Apples

1003 Pears

1004 Oranges

And then you executed a SELECT statement using the LISTAGG function:

SELECT LISTAGG(product_name, ', ') WITHIN GROUP (ORDER BY product_name) "Product_Listing"
FROM products;

You would get the following results:

Product_Listing

Apples, Bananas, Oranges, Pears

In this example, the results of the LISTAGG function are output in a single field with the values comma delimited.

You can change the ORDER BY clause to use the DESC keyword and change the SELECT statement as follows:

SELECT LISTAGG(product_name, ', ') WITHIN GROUP (ORDER BY product_name DESC) "Product_Listing"
FROM products;

This would give the following results:

Product_Listing

Pears, Oranges, Bananas, Apples

You could change the delimiter from a comma to a semi-colon as follows:

SELECT LISTAGG(product_name, '; ') WITHIN GROUP (ORDER BY product_name DESC) "Product_Listing"
FROM products;

This would change your results as follows:

Product_Listing

Pears; Oranges; Bananas; Apples