create or replace function getKey(v_id nvarchar)
return varchar
is
v_ret varchar(1000) := '';
v_xh varchar(10);
v_key varchar(10)
cursor v_c is
select xh,key
from tablename
where id = v_id;
begin
open v_c; fetch v_c into v_xh,v_key;
while v_c%FOUND LOOP
v_ret = v_ret||','||v_xh||v_key;
fetch v_c into v_xh,v_key;
end loop;
v_ret = substring(v_ret,1);
return v_ret;
end;select a.id,a.name,b.key
from table1 a
inner join (select id,getKey(id) key from table1 group by id) b
on a.id = b.id
return varchar
is
v_ret varchar(1000) := '';
v_xh varchar(10);
v_key varchar(10)
cursor v_c is
select xh,key
from tablename
where id = v_id;
begin
open v_c; fetch v_c into v_xh,v_key;
while v_c%FOUND LOOP
v_ret = v_ret||','||v_xh||v_key;
fetch v_c into v_xh,v_key;
end loop;
v_ret = substring(v_ret,1);
return v_ret;
end;select a.id,a.name,b.key
from table1 a
inner join (select id,getKey(id) key from table1 group by id) b
on a.id = b.id
所有的数据都列出来。
请大家多多帮助。谢谢表的结构是这样的:
id name xh key
01 ai 02 a
02 i 03 b
01 ai 03 b
现在把id,name一样的合成一行
id name key
01 ai 02a,03b
02 i 03b
select a.id,a.name,b.key
from table1 a
inner join (select id,getKey(id) key from table1 group by id) b
on a.id = b.id
上面的sql是表的内连接,把所有的id都合并了。