listagg wm_concat 行转列

最新推荐文章于 2025-06-13 14:37:42 发布

王小扣

最新推荐文章于 2025-06-13 14:37:42 发布

阅读量463

点赞数

CC 4.0 BY-SA版权

分类专栏： Oracle

本文链接：https://blog.youkuaiyun.com/qq_29150765/article/details/81300576

Oracle 专栏收录该内容

10 篇文章

订阅专栏

本文介绍如何使用SQL中的LISTAGG和WM_CONCAT函数将多行数据聚合为一行，并通过实例展示了如何按特定顺序进行聚合及去除重复项。此外，还提供了不使用GROUP BY的情况下的聚合方法。

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

一、

这个写法和wm_concat相似,
listagg(day,',')要把哪一列转换为同一行
within group (order by day)同一行如何排序

with temp as
 (
 select '1月' month, '1' day from dual union all
 select '2月' month, '1' day from dual union all
 select '2月' month, '2' day from dual union all
 select '3月' month, '1' day from dual union all
 select '3月' month, '2' day from dual union all
 select '3月' month, '3' day from dual 
 )
select 
 month,listagg(day,',')within group (order by day ) days
 from temp 
 group by month;

--去重复
regexp_replace((listagg(o.USER_NAME, '、') within group(order by s.start_date asc)), '([^、]+)(、\1)+', '\1') JY_NAME,

with temp as
 (
 select '1月' month, '1' day from dual union all
 select '2月' month, '1' day from dual union all
 select '2月' month, '2' day from dual union all
 select '3月' month, '1' day from dual union all
 select '3月' month, '2' day from dual union all
 select '3月' month, '3' day from dual 
 )
select 
   month,
   to_char(wm_concat(day))
 from temp 
group by month;

二、

不使用group by

with temp as
 (
 select '1月' month, '1' day from dual union all
 select '2月' month, '1' day from dual union all
 select '2月' month, '2' day from dual union all
 select '3月' month, '1' day from dual union all
 select '3月' month, '2' day from dual union all
 select '3月' month, '3' day from dual 
 )
select month , 
listagg(day,',')within group (order by day) over (partition by month) DAYS
from temp

with temp as
 (
 select '1月' month, '1' day from dual union all
 select '2月' month, '1' day from dual union all
 select '2月' month, '2' day from dual union all
 select '3月' month, '1' day from dual union all
 select '3月' month, '2' day from dual union all
 select '3月' month, '3' day from dual 
 )
select *
  from (select month,
               to_char(wm_concat(day) over(partition by month order by day)) days,
               row_number() over(partition by month order by day desc) rn
          from temp)
 where rn = 1