Turn: Oracle's Column change function: Listagg ()

Look at the sample code first:

SQL code

2. With temp as (
4. Select ' China ' nation,' Guangzhou ' to dual Union All
6. Select ' China ' nation,' Shanghai ' to dual Union All
8. Select ' China ' Nation,' Beijing ' to dual Union All
10. Select ' USA ' Nation,' New York ' to dual Union all
12. Select ' USA ' Nation,' Bostom ' city from dual Union All
14. Select ' Japan ' Nation,' Tokyo ' from dual
16. )
18. Select Nation,listagg (City,', ') within GROUP (order by city)
20. From temp
22. GROUP BY Nation

This is the most basic usage:

Listagg (xxx,xxx) within GROUP (ORDER by XXX)

Using a GROUP BY statement, a field of each group is stitched together, just like an aggregate function.

Very convenient.

Also an aggregation function, there is an advanced usage:

Is over (partition by XXX)

In other words, you can also use the Listagg function when you are not practical with the GROUP BY statement:

SQL code

2. With temp as (
4. Select population, ' China ' nation,' Guangzhou ' to dual Union All /c1>
6. Select population, ' China ' nation,' Shanghai ' to dual Union all
8. Select population, ' China ' Nation,' Beijing ' to dual Union all
10. Select population, ' USA ' Nation,' New York ' city from dual Union all
12. Select population, ' USA ' Nation,' Bostom ' city from dual Union all
14. Select population, ' Japan ' Nation,' Tokyo ' city from dual
16. )
18. Select population,
20. Nation
22. City
24. Listagg (city,', ') within GROUP [order by City ] Over (partition by Nation) rank
26. From temp

Summary: Listagg () uses it as a sum () function.

Turn: Oracle's Column change function: Listagg ()