Oracle 行转列 listagg() , wm_concat()...（连接字符串函数）

最新推荐文章于 2024-09-18 15:02:39 发布

Terry_5008

最新推荐文章于 2024-09-18 15:02:39 发布

阅读量1k

点赞数

分类专栏： oracle 文章标签：数据库

本文链接：https://blog.youkuaiyun.com/iteye_5008/article/details/82619027

版权

oracle 专栏收录该内容

11 篇文章

订阅专栏

本文介绍了Oracle数据库中三种用于字符串连接的函数：LISTAGG、wm_concat及sys_connect_by_path。详细解释了每种函数的使用场景、语法及注意事项，并通过实例展示了如何在不同情况下运用这些函数来提高数据处理效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、LISTAGG(XXX,XXX) WITHIN GROUP( ORDER BY XXX)

介绍：其函数在Oracle 11g 版本中推出，对分组后的数据按照一定的排序进行字符串连接。其中，“[,]”表示字符串连接的分隔符，如果选择使用[over (partition by )]则会使其变成分析函数；

使用注意：

SELECT c.gysid, listagg(c.wzlbid,',') within GROUP (ORDER BY c.wzlbid) FROM wzgl_gys_zrwmfw c GROUP BY gysid

这几个方法都可以实现行转列，达到想要的效果。

但当数据量比较大时却报ORA-01489:字符串连接的结果过长。

select aaa.aaa_id,
       listagg(aaa.line_id, ',') within GROUP(order by(aaa.line_id)) as ids
  from  aaa
 group by aaa.aaa_id;

结果

274	354011,354015,354016
275	355372,355375,355387

用法就像聚合函数一样，通过Group by语句，把每个Group的一个字段，拼接起来。

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

同样是聚合函数，还有一个高级用法：

就是over(partition by XXX)

也就是说，在你不实用Group by语句时候，也可以使用LISTAGG函数：

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 500 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

二、wm_concat(column)

介绍：其函数在Oracle 10g推出，在10g版本中，返回字符串类型，在11g版本中返回clob类型。括号里面的参数是列，而且可以是多个列的集合，也就是说在括号里面可以自由地用‘||’合并字符串。如下面的例子： Select u_id, wmsys.wm_concat(goods || '(' || num || '斤)' ) goods_sum from shopping group by u_id

select aaa.aaa_id, wm_concat(aaa.line_id) as ids
  from  aaa
 group by aaa.aaa_id;

结果

274	354011,354015,354016
275	355372,355375,355387

三、sys_connect_by_path(column,<分隔符>)

介绍：其函数在Oracle 9i 版本中推出，用来合并链路的字符串。注意的是其一定要和connect by子句合用！

第一个参数是形成树形式的字段，第二个参数是父级和其子级分隔显示用的分隔符。

以上内容转自：http://blog.sina.com.cn/s/blog_a637e97e01014pqo.html