oracle以逗号分隔,可以使用wm_concat,但该函数本身不能将分组后重复数据去重后,再以逗号分隔。
最近写sql中分组统计有这个需求,有位同事百度搜索了“oracle 逗号分隔 去重处理”,找到了解决方法。感谢大神,感谢同事!
以下内容转载自:http://aniyo.iteye.com/blog/2109260
sql结果集某列值显示如下:
'00010430, 00228290, 07046884,07046884, 07383953'
'00010430, 00228290,07046884, 07383953'
编写的函数如下:
create or replace function "FN_WM_CONCAT_DISTINCT" (input_str_list in varchar2)
return varchar2
is
output_str_list varchar2(5000);
begin
with accts as (
select distinct str from (
select regexp_substr(replace(input_str_list,', ',','), '[^,]+', 1, level, 'i') as str
from dual connect by level
<= length(replace(input_str_list,', ',','))
- length(regexp_replace(replace(input_str_list,', ',','), ',', ''))+1))
select wm_concat(str) into output_str_list from accts;
return output_str_list;
end;
调用的话,sql中只要使用fn_wm_concat_distinct(wm_concat(相关字段))即可