oracle不清楚,给你个sql server的例子eg2:
有表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
或者是从表B变成A(不要用游标)
以前有相似的列子,现在找不到了,帮帮忙!
--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
有表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
或者是从表B变成A(不要用游标)
以前有相似的列子,现在找不到了,帮帮忙!
--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
只有写程序,这类例子论坛上很多的,可以自己找找
return varchar2
is
col_c3 varchar2(4000);
begin
for cur in (select c from table_name where a=tmp_c1 and b=tmp_c2) loop
col_c3:=col_c3||cur.c;
end loop;
col_c3:=rtrim(col_c3,1);
return col_c3;
end;
/select a,b,linkstr(a,b) linkc from table_name group by a,b;
create type CatStringImpl as object
(
catstring VARCHAR2(4000), -- 返回的字符串
static function ODCIAggregateInitialize(sctx IN OUT CatStringImpl)
return number,
member function ODCIAggregateIterate(self IN OUT CatStringImpl,
value IN varchar2) return number,
member function ODCIAggregateTerminate(self IN CatStringImpl,
returnValue OUT varchar2, flags IN number) return number,
member function ODCIAggregateMerge(self IN OUT CatStringImpl,
ctx2 IN CatStringImpl) return number
);create or replace type body CatStringImpl is
static function ODCIAggregateInitialize(sctx IN OUT CatStringImpl )
return number is
begin
sctx := CatStringImpl('');
return ODCIConst.Success;
end;member function ODCIAggregateIterate(self IN OUT CatStringImpl, value IN varchar2)
return number is
begin
self.catstring := self.catstring || ',' || value;
return ODCIConst.Success;
end;member function ODCIAggregateTerminate(self IN CatStringImpl, returnValue OUT
varchar2, flags IN number) return number is
begin
returnValue := self.catstring;
return ODCIConst.Success;
end;member function ODCIAggregateMerge(self IN OUT CatStringImpl , ctx2 IN
CatStringImpl ) return number is
begin
self.catstring = self.catstring || ',' || ctx2.catstring ;
return ODCIConst.Success;
end;
end;
建函数
create function catstr( input varchar2 ) return varchar2 PARALLEL_ENABLE AGGREGATE USING CatStringImpl ;使用
select catstr( a) , b , c from tb group by b , c 就可以将字符串按照code组合起来.
;
insert into test_haor
values('001','002','a');
insert into test_haor
values('001','002','b');
insert into test_haor
values('001','002','c');
insert into test_haor
values('002','002','d');
insert into test_haor
values('002','002','e');
insert into test_haor
values('003','001','a');
insert into test_haor
values('004','002','b');
commit;
set serveroutput on size 1000000
declare
union_c varchar2(20);
begin
for cura in (select distinct a,b from test_haor) loop
for cur in (select c from test_haor where a=cura.a and b=cura.b) loop
union_c:=union_c||cur.c;
end loop;
dbms_output.put_line(cura.a||' '||cura.b||' '||union_c);
union_c :='';
end loop;
end;12:57:22 SQL> /
001 002 abc
002 002 de
003 001 a
004 002 b