ListAgg function – Oracle

List aggregation – LISTAGG()

It aggregates values from multiple rows into a single list.

Syntax:

LISTAGG(

Column [, Delimiter]

)

WITHIN GROUP (Order by Col_name)

  [Over(Partition by Col_name)]

This function comes handy when you are looking for an answer with concatenated strings.

Usage:

Select

ListAgg(department_name, ‘-‘) within group (order by

Depart_sort_no)

From department_master;

This query gives output of all department names in a – delimited list.

In some cases you have to deal with obtaining distinct values in the list.

Using the keyword distinct within the function gets the unique list.

ListAgg(distinct column_name)

Finally, how to deal with overflow. It means if the length of the string exceeds the standard data boundary. What should we do?

Let the system fail by throwing value exceed the length.

Do not worry, to do this there is an option in this function.

ON OVERFLOW TRUNCATE [<filler>] WITH[OUT] COUNT

This option is the last part of ListAgg()

Usage:

ListAgg(col1, ‘delimiter’ on overflow truncate)

Listagg(col1, ‘delimiter’, on overflow truncate with count)

Listagg(col1, ‘delimiter’, on overflow truncate without count)

Filler – can be used to represent obvious continuous characters such as …

ListAgg(col1, ‘delimiter’ on overflow …)

That is it, learn by doing, happy coding.