现在类似有套数据如下:
字段A ¦字段B ¦字段C
001, 002, a
001, 002, b
001, 002, c
002, 002, d
002, 002, e
003, 001, a
004, 002, b
要求得到如下结果:
001, 002, abc
002, 002, de
003, 001, a
004, 002, b
也就是对字段A字段B分组,同时在分组内。做字段C的拼接。
望解答。谢谢
---------------------------------------------------------------
CREATE OR REPLACE function linkstr(tmp_c1 varchar2,tmp_c2 varchar2)
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;
---------------------------------------------------------------
还有一种是在oracle9i以上才能用的,oracle9i可以加入聚合函数
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组合起来.
---------------------------------------------------------------
create table test_haor (a char(3),b char(3),c char(1))
;
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