SQL 列转行的实现

最新推荐文章于 2024-11-04 16:47:33 发布

转载最新推荐文章于 2024-11-04 16:47:33 发布 · 123 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：https://yq.aliyun.com/articles/647192

文章标签：

#数据库

--列转行，逗号拼接指定列的值

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;