How to use listagg function in Oracle

This article mainly introduces the use of the listagg function in Oracle, which is very detailed and has a certain reference value. Friends who are interested must finish it!

The usage of listagg function

This is a column-to-row function for Oracle: LISTAGG ()

Take a look at the sample code first:

Sql code

With temp as (select 'China' nation,' Guangzhou' city from dual union all select 'China' nation,' Shanghai' city from dual union all select 'China' nation,' Beijing' city from dual union all select 'USA' nation,' New York' city from dual union all select 'USA' nation,' Bostom' city from dual union all select 'Japan' nation,' Tokyo' city from dual) select nation,listagg (city,',') within GROUP (order by city) from temp group by nation

This is the most basic usage:

LISTAGG (XXX,XXX) WITHIN GROUP (ORDER BY XXX)

The usage is like an aggregate function, stitching together a field of each Group through a Group by statement.

It's very convenient.

It is also an aggregate function, and there is an advanced usage:

It's over (partition by XXX).

That is, when you do not use the Group by statement, you can also use the LISTAGG function:

Sql code

With temp as (select 500 population, 'China' nation,' Guangzhou' city from dual union all select 1500 population, 'China' nation,' Shanghai' city from dual union all select 1000 population, 'China' nation,' Beijing' city from dual union all select 1000 population, 'USA' nation,' New York' city from dual union all select 500 population, 'USA' nation,' Bostom' city from dual union all select 500 population, 'Japan' nation,' Tokyo' city from dual) select population, nation City, listagg (city,',') within GROUP (order by city) over (partition by nation) rank from temp and above are all the contents of the article "how to use the listagg function in Oracle" Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!