oracle 多行合并并且取最长的一条

Create table Merge_test
(
LB varchar2(10),
DES varchar2(20)
)

insert into Merge_test
values
(
'd',null

..........
);
commit;

格式如下

--合并数据
SELECT LB, LTRIM(MAX(SYS_CONNECT_BY_PATH(DES, ',')), ',') B
FROM (SELECT LB, des, ROW_NUMBER() OVER(PARTITION BY LB ORDER BY LB DESC) RN
FROM Merge_test)
START WITH RN = 1
CONNECT BY RN - 1 = PRIOR RN
AND LB = PRIOR LB
GROUP BY LB;

取b最长的保留

 

create or replace function MergeString
(
p_string in varchar2,
p_deli in varchar2

)
return varchar2
is
v_length NUMBER ;
v_start NUMBER ;
v_index NUMBER;
v_max number;
v_result varchar2(500);
Begin
v_length:= LENGTH(p_string);
v_start:=1;
if Instr(p_string,p_deli,v_start)<>0
then
v_result:=substr(p_string,v_start,Instr(p_string,p_deli,v_start)-1);
else
v_result:=substr(p_string,v_start);
end if;
v_max:=length(v_result);

while v_start <= v_length
loop
v_index:=Instr(p_string,p_deli,v_start);
if v_index=0
then
if Length(v_result)<Length(substr(p_string,v_start))
then
v_result:=substr(p_string,v_start,v_length);
end if;
v_start:=v_length+1;
else
if v_max<=v_index-v_start
then
v_max :=v_index-v_start;
v_result:=substr(p_string,v_start,v_index-v_start);
end if;
v_start:=v_index+1;
end if;
end loop;
--Dbms_Output.put_line('v_result0:'||v_result);
return(v_result);
end;

--查看结果

--合并数据
with
sql1 as (SELECT LB, LTRIM(MAX(SYS_CONNECT_BY_PATH(DES, ',')), ',') B
FROM (SELECT LB, des, ROW_NUMBER() OVER(PARTITION BY LB ORDER BY LB DESC) RN
FROM Merge_test)
START WITH RN = 1
CONNECT BY RN - 1 = PRIOR RN
AND LB = PRIOR LB
GROUP BY LB)

select LB,mergestring(b,',') from sql1