Oracle数据库如何将表的某一列所有值用逗号隔开去重后合并成一行_Oracle

一、背景

最近在工作中，有个需求是要求在oracle统计查询的时候，将表的某一列的所有值用逗号隔开，去重后合并成一行。于是研究了一下listagg和xmlagg 函数用来合并数据

以下通过实例说明。

二、方法

1.不去重的两种方法

listagg函数

返回结果为varchar2格式的数据，即拼接后的字符串最大可以保存4000字节的数据，所以大于这个数据的字符串就会报ora-01489 字符串连接的结果过长的错误。

xmlagg函数

当查询结果过长，拼接的字符串长度过长大于4000字节，我们可以使用这个函数，函数返回结果为clob类型，大对象数据类型最大可以存储4gb的数据长度。

用法1：用某符号拼接列中所有值

select
    listagg(student_name, ',') within group(order by student_name) listagg
from
    student_info t;

执行结果:

listagg

王一,陈二,张三,李四

用法2：按某列分组，用指定符号拼接组内列中所有值

//方法1 listagg
select
    t.student_name,t.student_sex
    listagg(student_name, ',') within group(order by student_name) 
    over( partition by student_sex) 
    listagg
from
    student_info t;

//方法2 xmlagg

select
    xmlagg(xmlparse(content t.student_name || ',' wellformed) order by t.student_name).getclobval()     
    listagg
from
    student_info t;

执行结果:

student_name	student_sex	listagg
王一	女	王一,陈二
陈二	女	王一,陈二
张三	男	张三,李四
李四	男	张三,李四

2.去重的方法

数据表

创建日期	公司代码	销售物品
20230718085055	111	苹果
20230718085055	111	香蕉
20230718090000	111	苹果
20230718090000	112	牙刷
20230718090000	112	牙刷

若不去重则显示数据为

公司代码	销售物品
111	苹果,香蕉,苹果
112	牙刷,牙刷

有多种去重方法,这边建议先去重再聚合

//distinct去重后,再聚合 拼接
    select
        t.company_code ,listagg(t.sale_name, ',') within group (
        order by  t.sale_name) over(partition by t.company_code ) listagg 
    from 
      (
        select
          distinct s.sale_name,
          s.company_code 
        from
          sale_info s
     ) t

执行结果: