Oracle列转成逗号分隔字符串

yaoboy 贡献于2011-10-05

作者 yaoqingqing4551  创建于2011-09-27 01:24:00   修改者yaoqingqing4551  修改于2011-09-27 01:24:00字数1635

文档摘要:
关键词:

Oracle将表格的某一列转换成逗号分隔的字符串 2011-05-12 18:18 --依次创建以下函数 CREATE OR REPLACE FUNCTION stragg(input varchar2) RETURN varchar2   PARALLEL_ENABLE   AGGREGATE USING string_agg_type; --1 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 ) --2 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 := s elf.total || ctx2.total;     return ODCIConst.Success;   end; end; --------------------------------------- --测试如下: create table t_number (id  number) insert into t_number(id) values(1); commit; insert into t_number(id) values(2); commit; insert into t_number(id) values(3); commit; insert into t_number(id) values(4); commit; insert into t_number(id) values(5); commit; --修改前: select * from t_number;    id 1  1 2  2 3  3 4  4 5  5 --修改后: select stragg(distinct id) id from t_number;    id 1  1,2,3,4,5

下载文档到电脑,查找使用更方便

文档的实际排版效果,会与网站的显示效果略有不同!!

需要 3 金币 [ 分享文档获得金币 ] 1 人已下载

下载文档