create or replace function sum_string(v_sql varchar2) return varchar2 as type cur_alldata is ref cursor; l_alldata cur_alldata; v_row varchar2(99); v_sum varchar2(3999); begin open l_alldata for v_sql; loop fetch l_alldata into v_row; exit when l_alldata%notfound; v_sum := v_sum||';'||v_row; end loop; v_sum := substr(v_sum,2); close l_alldata; return v_sum; end; // select sum_string( 'select name from a' ) from dual
select GroupCol, replace(max(sys_connect_by_path(ConCol,',')),',',';') from (SELECT GroupCol, ConCol, MIN(ConCol) over(PARTITION BY GroupCol) minConCol ,(row_number() over(ORDER BY GroupCol, ConCol)) + (dense_rank() over(ORDER BY GroupCol)) no FROM tally) start with ConCol=minConCol connect by no-1 = prior no group by GroupCol --说明:GroupCol ,分组的列;ConCol,要连接字符串的列
我测试是成功的,试试看~~ create table li( id number, name varchar2(5) );
insert into li values(1,'李1'); insert into li values(2,'李2'); insert into li values(3,'李3');
commit; select distinct ltrim(first_value(path) over(order by lev desc),';') name from ( select c.*, Level lev, sys_connect_by_path(c.name,';') path
from ( select l.*, (row_number() over(order by id))-1 rn_f, row_number() over(order by id) rn_a from li l )c connect by prior rn_a = rn_f );
return varchar2
as
type cur_alldata is ref cursor;
l_alldata cur_alldata;
v_row varchar2(99);
v_sum varchar2(3999);
begin
open l_alldata for v_sql;
loop
fetch l_alldata into v_row;
exit when l_alldata%notfound;
v_sum := v_sum||';'||v_row;
end loop;
v_sum := substr(v_sum,2);
close l_alldata;
return v_sum;
end;
//
select sum_string( 'select name from a' ) from dual
(SELECT GroupCol, ConCol, MIN(ConCol) over(PARTITION BY GroupCol) minConCol
,(row_number() over(ORDER BY GroupCol, ConCol)) + (dense_rank() over(ORDER BY GroupCol)) no
FROM tally)
start with ConCol=minConCol
connect by no-1 = prior no
group by GroupCol
--说明:GroupCol ,分组的列;ConCol,要连接字符串的列
id number,
name varchar2(5)
);
insert into li values(1,'李1');
insert into li values(2,'李2');
insert into li values(3,'李3');
commit; select distinct ltrim(first_value(path) over(order by lev desc),';') name
from (
select c.*,
Level lev,
sys_connect_by_path(c.name,';') path
from (
select l.*,
(row_number() over(order by id))-1 rn_f,
row_number() over(order by id) rn_a
from li l
)c
connect by prior rn_a = rn_f
);