Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

How to use listagg function in Oracle

2025-04-01 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)05/31 Report--

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!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report