oracle string aggregator

字符串汇总：把多行的字符串汇总到单行(用,分割符)

1.可以使用递归SQL实现

测试：

create table test(ename varchar(100),duty_date date);
insert into test values('aa',trunc(sysdate)-15);
insert into test values('aa',trunc(sysdate)-13);
insert into test values('aa',trunc(sysdate)-5);
insert into test values('bb',trunc(sysdate)-11);
insert into test values('bb',trunc(sysdate)-3);
insert into test values('cc',trunc(sysdate)-8);
insert into test values('cc',trunc(sysdate)-2);

SQL如下：

select ename, duty_cnt, mduty_date
  from (select ename,
               duty_cnt,
               mduty_date,
               row_number() over(partition by ename order by length(mduty_date) desc) rnk
          from (select ename,
                       duty_cnt,
                       substr(sys_connect_by_path(to_number(to_char(duty_date,
                                                                    'dd')),
                                                  ','),
                              2) mduty_date
                  from (select ename,
                               duty_date,
                               count(ename) over(partition by ename) duty_cnt,
                               row_number() over(partition by ename order by duty_date) rid
                          from test) t
                connect by ename = prior ename
                       and rid - 1 = prior rid))
 where rnk = 1

 

2.用tom的stragg：

--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 := self.total || ctx2.total;  
    return ODCIConst.Success;  
  end;  
end; 

--3

CREATE OR REPLACE FUNCTION stragg(input varchar2) RETURN varchar2  
  PARALLEL_ENABLE  
  AGGREGATE USING string_agg_type;  

 

定义好后，就可以使用stragg函数进行字符串汇总，使用方法同sum,count等函数。

select ename,stragg(to_number(to_char(duty_date,'dd'))) mduty_date,count(*) from test
group by ename

3.自己根据需求写存储过程也是可以的。