oracle 行变列函数

--该sql生成了一个名为list的函数。功能是将多行的数据记录转换成一行的记录，各数据用“参数的第一位字符隔开”分隔。
--使用样例：select ColumnToRow(','||Tname) from tab where rownum<10;

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;

select * from users;

评论

1 楼 ryanofanny 2009-03-19  

晕。写那么多..还以为找到了，你那么多其实只要一句话就可以实现
select wmsys.wm_concat(Tname) from tab