oracle list agg,ORACLE列变行

create or replace type list_agg as object

(

total varchar2(4000),

static function ODCIAggregateInitialize(sctx IN OUT list_agg)

return number,

member function ODCIAggregateIterate(self IN OUT list_agg,

value IN varchar2) return number,

member function ODCIAggregateTerminate(self IN list_agg,

returnValue OUT varchar2,

flags IN number)

return number,

member function ODCIAggregateMerge(self IN OUT list_agg,

ctx2 IN list_agg) return number

);

create or replace type body list_agg is

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

begin

sctx := list_agg(null);

return ODCIConst.Success;

end;

member function ODCIAggregateIterate(self IN OUT list_agg,

value IN varchar2) return number is

begin

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

return ODCIConst.Success;

end;

member function ODCIAggregateTerminate(self IN list_agg,

returnValue OUT varchar2,

flags IN number) return number is

begin

returnValue := substr(self.total, 2);

return ODCIConst.Success;

end;

member function ODCIAggregateMerge(self IN OUT list_agg,

ctx2 IN list_agg) return number is

begin

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

return ODCIConst.Success;

end;

end;

CREATE OR REPLACE FUNCTION ColumnToRow(input varchar2 )

RETURN varchar2

PARALLEL_ENABLE AGGREGATE USING list_agg;

测试语句:

select ColumnToRow(','||Tname) from tab where rownum<10;