oracle 列转行字符串,oracle 字符串列转行

通过这个函数，可以将两行记录，编程一行记录。

如： 散列：code，no，type

1 F A001

1 F A002

select  code, min( no)  no,

stragg(Distinct  type)  types

from tableName

结果： 1 F A001,A002

第一步，建立函数：

CREATE OR REPLACE FUNCTION stragg(input varchar2 )

RETURN varchar2

PARALLEL_ENABLE AGGREGATE USING string_agg_type;

第二步，建立类型 string_agg_type

CREATE OR REPLACE TYPE "STRING_AGG_TYPE" as object

(

total varchar2(4000),

static function

ODCIAggregateInitialize(sctx IN OUT string_agg_type )

return number,

member function

ODCIAggregateIterate(self IN OUT string_agg_type , value IN varchar2 )

return number,

member function

ODCIAggregateTerminate(self IN string_agg_type, returnValue OUT varchar2, flags IN number)

return number,

member function

ODCIAggregateMerge(self IN OUT string_agg_type, ctx2 IN string_agg_type)

return number

)

第三步：修改typebody内容

create or replace type body string_agg_type

Is

static function ODCIAggregateInitialize(sctx IN OUT string_agg_type)

return number

is

begin

sctx := string_agg_type( null );

return ODCIConst.Success;

end;

member function ODCIAggregateIterate(self IN OUT string_agg_type,value IN varchar2 )

return number

is

begin

self.total := self.total || ',' || value;

return ODCIConst.Success;

end;

member function ODCIAggregateTerminate(self IN string_agg_type,returnValue OUT varchar2,flags IN number)

return number

is

begin

returnValue := ltrim(self.total,',');

return ODCIConst.Success;

end;

member function ODCIAggregateMerge(self IN OUT string_agg_type,ctx2 IN string_agg_type)

return number

is

begin

self.total := self.total || ctx2.total;

return ODCIConst.Success;

end;

end;