--列转行，逗号拼接指定列的值
SQL Server中写法：
SELECT STUFF(( SELECT ',' + Field1 from TableA FOR XML PATH('')), 1, 1, '')
Oracle中写法：
方法一：wmsys.wm_concat
select wmsys.wm_concat(Field1) from TableA
方法二：LISTAGG()
2.1、LISTAGG(XXX,XXX) WITHIN GROUP( ORDER BY XXX)
用法就像聚合函数一样，通过Group by语句，把每个Group的一个字段，拼接起来
1 with temp as(
2 select 'China' nation,'Beijing' city from dual union
3 select 'China' nation,'Shanghai' city from dual union
4 select 'China' nation,'Guangzhou' city from dual union
5 select 'USA' nation,'New York' city from dual union
6 select 'USA' nation,'Bostom' city from dual
7 )
8 select nation,listagg(city,',') within group(order by city)
9 from temp
10 group by nation;
2.2、over(partition by XXX)
在不使用Group by语句时候，使用LISTAGG函数（当作SUM()函数来使用）
1 with temp as(
2 select 'China' nation,'Beijing' city from dual union
3 select 'China' nation,'Shanghai' city from dual union
4 select 'China' nation,'Guangzhou' city from dual union
5 select 'USA' nation,'New York' city from dual union
6 select 'USA' nation,'Bostom' city from dual
7 )
8 select nation,city,listagg(city,',') within group(order by city) over(partition by nation) rank
9 from temp;
如果您看了本篇博客,觉得对您有所收获，请点击右下角的 [推荐]
如果您想转载本博客，请注明出处
如果您对本文有意见或者建议，欢迎留言
感谢您的阅读，请关注我的后续博客
