Oracle / PLSQL: LISTAGG Function

Oracle / PLSQL: LISTAGG Function

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:

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:

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

By Kamar