Oracle column Change function Listagg ()

This is an Oracle's column change function: Listagg ()

Look at the sample code first:

With temp as (  select ' China ' Nation, ' Guangzhou ' to dual union ALL  Select ' China ' Nation, ' Shanghai ' city F ROM Dual UNION All  Select ' China ' Nation, ' Beijing ' to dual union ALL  Select ' USA ' Nation, ' New York ' city From dual UNION ALL  Select ' USA ' Nation, ' Bostom ' to dual union ALL  Select ' Japan ' Nation, ' Tokyo ' city fro M dual) Select Nation,listagg (City, ', ') within GROUP [order by City] from Tempgroup 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:

With temp as (  Select $ population, ' China ' nation, ' Guangzhou ' City from dual union All  Select population, ' China ' nation, ' Shanghai ' to dual union ALL  Select + population, ' China ' nation, ' Beijing ' city from dual UN Ion all  Select population, ' USA ' Nation, ' New York ' to dual union ALL Select +  population, ' USA ' NAT Ion, ' Bostom ' City from dual union All  Select population, ' Japan ' Nation, ' Tokyo ' to dual ' select Populatio N,       Nation, City       ,       Listagg (city, ', ') within GROUP (order by city) over (partition by nation) Rankfrom Temp

　　

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

Original: http://dacoolbaby.iteye.com/blog/1698957