Oracle多行数据拼接成一行

1、WM_CONCAT(需要拼接的字段名) 

Oracle 11.2以前推荐使用，11.2出现替代函数，12c已经移除。

默认使用逗号分隔，如需使用其他分隔符请看方法2。

--部分版本返回varchar2,部分版本返回clob
--1、不需要去重
select to_char(wm_concat(id)) 
from (
select '001' as id from dual
union all
select '002' as id from dual
union all
select '003' as id from dual
union all
select '001' as id from dual
union all
select '002' as id from dual
union all
select '003' as id from dual
)；
--返回结果：001,002,003,001,002,003

--2、去重
select to_char(wm_concat(distinct id)) 
from (
select '001' as id from dual
union all
select '002' as id from dual
union all
select '003' as id from dual
union all
select '001' as id from dual
union all
select '002' as id from dual
union all
select '003' as id from dual
)；
--返回结果：001,002,003

2、LISTAGG(需拼接的字段名,分隔符) WITHIN GROUP(ORDER BY 按照某个或某几个字段排序顺序进行拼接)

Oracle 11.2才有该函数，推荐使用。

LISTAGG报 ORA-01489: result of string concatenation is too long ，报错请使用方法3。

报错原因：listagg返回varchar2类型的值，varchar2最长只有4000个字符长度，超过4000则报错。

--1、不去重
select id
,listagg(name,',') within group(order by name) as name
from (
select '001' as id,'aaa' as name from dual
union all
select '001' as id,'aaa' as name from dual
union all
select '001' as id,'bbb' as name from dual
union all
select '002' as id,'ccc' as name from dual
union all
select '002' as id,'aaa' as name from dual
union all
select '002' as id,'aaa' as name from dual
)
group by id;
--返回结果：
--id    name
--001	aaa,aaa,bbb
--002	aaa,aaa,ccc


--2、去重
--可以先在子查询用group by 或 distinct 去重后再使用 listagg 拼接，推荐使用该方法，该方法较为简单此处不予演示
--此处演示使用正则进行去重
select id
,regexp_replace(listagg(name,',') within group(order by name),'([^,]+)(,\1)','\1') as name
from (
select '001' as id,'aaa' as name from dual
union all
select '001' as id,'aaa' as name from dual
union all
select '001' as id,'bbb' as name from dual
union all
select '002' as id,'ccc' as name from dual
union all
select '002' as id,'aaa' as name from dual
union all
select '002' as id,'aaa' as name from dual
)
group by id;

--返回结果：
--ID	NAME
--001	aaa,bbb
--002	aaa,ccc

3、XMLAGG  将多行数据拼接成一个xml类型的值

--1、不去重
select id
,rtrim( --去除最右边的逗号
   xmlcast( --将xml转为clob
     xmlagg( --拼接
       xmlelement(e, name || ',') order by name
     ) as clob
   ), ','
 ) as name
from (
select '001' as id,'aaa' as name from dual
union all
select '001' as id,'aaa' as name from dual
union all
select '001' as id,'bbb' as name from dual
union all
select '002' as id,'ccc' as name from dual
union all
select '002' as id,'aaa' as name from dual
union all
select '002' as id,'aaa' as name from dual
)
group by id;


--2、去重
select id
,regexp_replace(
  rtrim( --去除最右边的逗号
     xmlcast( --将xml转为clob
       xmlagg( --拼接
         xmlelement(e, name || ',') order by name
       ) as clob
     ), ','
   ),'([^,]+)(,\1)','\1'
 ) as name
from (
select '001' as id,'aaa' as name from dual
union all
select '001' as id,'aaa' as name from dual
union all
select '001' as id,'bbb' as name from dual
union all
select '002' as id,'ccc' as name from dual
union all
select '002' as id,'aaa' as name from dual
union all
select '002' as id,'aaa' as name from dual
)
group by id