有人問(wèn)這樣的sql該怎么實(shí)現(xiàn)：
表數(shù)據(jù)和結(jié)構(gòu)
?? ?CODE?NAME????B01????S01????B02????S02
????1??????????張三???????數(shù)學(xué)????80??
????1??????????張三????????????????????????????語(yǔ)文????75
????2??????????王五???????數(shù)學(xué)????70??
????2??????????王五????
????3??????????李四???????數(shù)學(xué)????50??
????3??????????李四???????????????????????????語(yǔ)文????88

希望查詢出如下結(jié)果：
?? ?CODE?SUM_STR(NAME)????B01????SUM_STR(S01)????B02????SUM_STR(S02)
????1????????????????張三????????????????????數(shù)學(xué)????????????????80?????????? 語(yǔ)文????????????75
????2????????????????王五????????????????????數(shù)學(xué)????????????????70??
????3????????????????李四????????????????????數(shù)學(xué)????????????????50?????????? 語(yǔ)文?????????????88
這個(gè)問(wèn)題可以采用自定義的聚集函數(shù)來(lái)實(shí)現(xiàn)：

create ? or ? replace ?type?strcat_type? as ?object?(
????cat_string? varchar2 ( 4000 ),
????static? function ?ODCIAggregateInitialize(cs_ctx? In ?Out?strcat_type)? return ? number ,
????member? function ?ODCIAggregateIterate(self? In ?Out?strcat_type,value? in ? varchar2 )? return ?

number ,
????member? function ?ODCIAggregateMerge(self? In ?Out?strcat_type,ctx2? In ?Out?strcat_type)?

return ? number ,
????member? function ?ODCIAggregateTerminate(self? In ?Out?strcat_type,returnValue?Out?

varchar2 ,flags? in ? number )? return ? number
)
/

------------------------------------

create ? or ? replace ?type?body?strcat_type? is
??static? function ?ODCIAggregateInitialize(cs_ctx? IN ?OUT?strcat_type)? return ? number
?? is
?? begin
??????cs_ctx?: = ?strcat_type(? null ?);
?????? return ?ODCIConst.Success;
?? end ;

??member? function ?ODCIAggregateIterate(self? IN ?OUT?strcat_type,
???????????????????????????????????????value? IN ? varchar2 ?)
?? return ? number
?? is
?? begin
?????? if ?self.cat_string? is ? null ? then
?????????self.cat_string?: = ?value;
?????? end ? if ;
?????? return ?ODCIConst.Success;
?? end ;

??member? function ?ODCIAggregateTerminate(self? IN ?Out?strcat_type,
?????????????????????????????????????????returnValue?OUT? varchar2 ,
?????????????????????????????????????????flags? IN ? number )
?? return ? number
?? is
?? begin
??????returnValue?: = ?self.cat_string;
?????? return ?ODCIConst.Success;
?? end ;

??member? function ?ODCIAggregateMerge(self? IN ?OUT?strcat_type,
?????????????????????????????????????ctx2? IN ?Out?strcat_type)
?? return ? number
?? is
?? begin
?????? if self.cat_string is null then
?????????????????? self.cat_string :=? ctx2.cat_string;
????????? end if;
?????? return ?ODCIConst.Success;
?? end ;

end ;
/

-------------------

CREATE ? OR ? REPLACE ? FUNCTION ?sum_str(input? varchar2 ?)
RETURN ? varchar2
PARALLEL_ENABLE?AGGREGATE?USING?strcat_type;
/

-------最后查詢語(yǔ)句：

select ?code,sum_str(name),?sum_str(b01)?b01,sum_str(s01)?,sum_str(b02)?b02,sum_str(s02)
from ?javaeye? group ? by ?code? order ? by ?code