半夏微凉

半夏微凉

oracle 多行并一行方法解析

1.聚合函数法。


/*TYPE*/

create or replace type strcat_type as object 

      (

        currentstr varchar2(4000), 

        currentseprator varchar2(8), 

        static function ODCIAggregateInitialize(sctx IN OUT strcat_type) return number,

        member functioan ODCIAggregateIterate(self IN OUT strcat_type,value IN VARCHAR2) return number,

        member function ODCIAggregateTerminate(self IN strcat_type,returnValue OUT VARCHAR2, flags IN number) return number,

        member function ODCIAggregateMerge(self IN OUT strcat_type,ctx2 IN strcat_type) return number

      );

      

create or replace type body strcat_type is 

      static function ODCIAggregateInitialize(sctx IN OUT strcat_type) return number is 

      begin

        sctx := strcat_type('',',');

        return ODCIConst.Success;

      end;

      member function ODCIAggregateIterate(self IN OUT strcat_type, value IN VARCHAR2) return number is

      begin

        if self.currentstr is null then

           self.currentstr := value;

        else

          self.currentstr := self.currentstr ||currentseprator || value;

        end if;

        return ODCIConst.Success;

      end;

      member function ODCIAggregateTerminate(self IN strcat_type, returnValue OUT VARCHAR2, flags IN number) return number is

      begin

        returnValue := self.currentstr;

        return ODCIConst.Success;

      end;

      member function ODCIAggregateMerge(self IN OUT strcat_type, ctx2 IN strcat_type) return number is

      begin

        if ctx2.currentstr is null then

          self.currentstr := self.currentstr;

        elsif self.currentstr is null then 

          self.currentstr := ctx2.currentstr;

        else

          self.currentstr := self.currentstr || currentseprator || ctx2.currentstr;

        end if; 

        return ODCIConst.Success;

      end;

      end;

/*函数*/    

CREATE OR REPLACE FUNCTION strcat (input VARCHAR2) RETURN VARCHAR2 PARALLEL_ENABLE AGGREGATE USING strcat_type;


例:SELECT t.id,strcat(t.name) FROM t_avg t GROUP BY t.id



2.listagg函数法。(oracle 11gr2及以上版本)


例: select id,listagg(name,',') within group(order by id) from t_avg t group by id;


2015-05-20 0 /
数据库
/
标签: 

评论回复


·