2. 行列转换--合并有表A,
id pid
1 1
1 2
1 3
2 1
2 2
3 1如何化成表B:
id pid
1 1,2,3
2 1,2
3 1创建一个合并的函数
create function fmerg(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+cast(pid as varchar) from 表A where id=@id
set @str=right(@str,len(@str)-1)
return(@str)
End
go--调用自定义函数得到结果
select distinct id,dbo.fmerg(id) from 表A 怎么把这个sqlserver函数转换成oracle中的函数---------谢谢
id pid
1 1
1 2
1 3
2 1
2 2
3 1如何化成表B:
id pid
1 1,2,3
2 1,2
3 1创建一个合并的函数
create function fmerg(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+cast(pid as varchar) from 表A where id=@id
set @str=right(@str,len(@str)-1)
return(@str)
End
go--调用自定义函数得到结果
select distinct id,dbo.fmerg(id) from 表A 怎么把这个sqlserver函数转换成oracle中的函数---------谢谢
returns varchar2
as
str varchar(8000);
beginfor x in select * from tableA
loop
str:=str||to_char(substr)||',';
end loop;
return str;
End
中的x是什么意思 没法运行
TYPE type_cur IS REF cursor;
l_search_cur type_cur;
l_pid varchar2(3000);
l_result varchar2(3000);
BEGIN
OPEN l_search_cur FOR
select name from vt_userid_info where userid = userid_in and virtualteamid=vtid_in;
LOOP
FETCH l_search_cur INTO l_pid;
EXIT WHEN l_search_cur%NOTFOUND;
l_result := l_result || l_pid || ',';
END LOOP;
l_result := substr(l_result,1,length(l_result)-1);
return l_result;
END test;
可以运行的答案