Oracle实现字符串相加函数有如下表，实现相同Account 的DSPNAME值相加
Account DSPNAME
L1          aa
L1          bb
L2          cc
结果L1          aa,bb
L2          cc
⽅法1：在函数中使⽤cursor
CREATE OR REPLACE FUNCTION SDA.FNROLE (ACC in varchar2)
return varchar2is
Result varchar2(500);
cursor rad_cursor is
select DSPNAME from SDA_USER_ROLE
where LOWER(Account) =LOWER(ACC);
begin
for rad_val in rad_cursor
loop
Result:=Result ||','|| rad_val.DSPNAME;
end loop;
RESULT:=SUBSTR(Result,2);
return(Result);
oracle切割字符串函数end FNROLE;
使⽤select sda.fnrole('1') AS A from dual;
⽅法2：使⽤oracle分析函数转⾃ miclu
City People Make
⼴州  1        A
⼴州  2        B
⼴州  3        C
上海  4        A
上海  5        E
⼴州  6        A
上海  7        E
select City,sum(People) as People,ZH_SPLIT(ltrim(max(sys_connect_by_path(Make,',')),','),',') as Make from (
select City,People,Make,
rank()over(order by City) + row_number()over(order by City) RN,
row_number()over(partition by City order by City) RM
from SRS_B_CW_TEST
)
start with RM=1
connect by prior RN=RN-1
group by
City
写⼀个函数去除重复值
/************************************************************************************
创建者：曾浩
创建时间：2007-9-27
最新修改者：曾浩
最新修改时间：2007-9-27
⽤途：改进的split函数,
输⼊字符串123,123,234,345,234,345,456和字符串,
输出123,234,345,456
************************************************************************************/
create or replace function ZH_SPLIT(v_string in varchar2, v_delimiter in varchar2)
return varchar2
is
j int:=0;
i int:=1;
len_string int:=0;
len_delimiter int:=0;
str varchar2(4000);
v_return varchar2(4000);
begin
len_string := LENGTH(v_string);
len_delimiter := LENGTH(v_delimiter);
while j < len_string
loop
j := INSTR(v_string, v_delimiter, i);
if j = 0 then
j := len_string;
str := SUBSTR(v_string, i);
if instr(v_return, str) > 0 then
null;
else
v_return:=v_return||str||',';
end if;
if i >= len_string then
exit;
end if;
else
str := SUBSTR(v_string, i, j - i);
i := j + len_delimiter;
if instr(v_return, str) > 0 then
null;
else
v_return:=v_return||str||',';
end if;
end if;
end loop;
v_return := substr(v_return, 1, length(v_return)-1);
return v_return;
end;
⽅法3：如果ID列为数字，可免去去除重复值的函数create table TEST AS
(select 1 ID, '11111' MC from dual
union
select 1, '22222'from dual
union
select 2, '11111'from dual
union
select 2, '22222'from dual
union
select 3, '11111'from dual
union
select 3, '22222'from dual
union
select 3, '33333'from dual );
select id,ltrim(max(sys_connect_by_path(mc,',')),',') row2col from (select id,mc,
ID+(row_number() over(order by id)) node_id,
row_number() over(partition by id order by id) rn
from test)
start with rn = 1
connect by node_id-1 = prior node_id
group by id
order by id;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。