有个表,记录如下
字段1 字段2
11 'a'
11 'b'
12 'a'
12 'c'
12 'f'
14 'b'要查询处如下格式怎么处理
字段1 字段2
11 'ab'
12 'acf'
14 'b'
字段1 字段2
11 'a'
11 'b'
12 'a'
12 'c'
12 'f'
14 'b'要查询处如下格式怎么处理
字段1 字段2
11 'ab'
12 'acf'
14 'b'
http://dev-club.esnai.com/club/bbs/showEssence.asp?id=26382&page=1
结贴给分
---------- --------------------
1 AAA
2 BBB
1 ABC
3 CCC
2 DDDSQL> select id, f_sumstr(name) name from test group by id; ID NAME
---------- ------------------------------------------------------
1 AAAABC
2 BBBDDD
3 CCC
create or replace type SumStr as object
(
str varchar2(30000),
static function odciaggregateinitialize(sctx in out SumStr) return number,
member function odciaggregateiterate(self in out SumStr, value in varchar2) return number,
member function odciaggregateterminate(self in out SumStr, returnvalue out varchar2, flags in number) return number,
member function odciaggregatemerge(self in out SumStr, ctx2 in SumStr) return number
)
/create or replace type body SumStr is
static function odciaggregateinitialize(sctx in out SumStr)
return number is
begin
sctx := SumStr(null);
return odciconst.Success;
end;
member function odciaggregateiterate(self in out SumStr, value in varchar2)
return number is
begin
self.str := self.str || value;
return odciconst.Success;
end;
member function odciaggregateterminate(self in out SumStr, returnvalue out varchar2, flags in number)
return number is
begin
returnvalue := self.str;
return odciconst.Success;
end;
member function odciaggregatemerge(self in out SumStr, ctx2 in SumStr)
return number is
begin
null;
return odciconst.Success;
end;
end;
/create or replace function F_SumStr(p_str varchar2) return varchar2
aggregate using SumStr;