Group string addition in Database

-- This test script can be run directly.

-- Now we want to group and add data in the database by fixed fields. Five methods are summarized here.

-- Create a test table and add Test Data

 
 

  
  
create table test(id varchar2(10),mc varchar2(50));   
  
  
insert into test values('1','11111');   
  
  
insert into test values('1','22222');   
  
  
insert into test values('2','11111');   
  
  
insert into test values('2','22222');   
  
  
insert into test values('3','11111');   
  
  
insert into test values('3','22222');   
  
  
insert into test values('3','33333');   
  
  
commit; 
 
 

-- Method 1:

 
 

  
  
set serveroutput on size 1000000   
  
  
declare   
  
  
union_mc varchar2(200);   
  
  
begin   
  
  
for cur_a in(select distinct id from test) loop   
  
  
for cur_b in(select mc from test where id=cur_a.id) loop   
  
  
union_mc:=union_mc||cur_b.mc;   
  
  
end loop;   
  
  
dbms_output.put_line(cur_a.id||chr(9)||union_mc);   
  
  
union_mc := '';   
  
  
end loop;   
  
  
end;   
  
  
/  
 
 

-- Method 2:

 
 

  
  
CREATE OR REPLACE function link(v_id varchar2)   
  
  
return varchar2   
  
  
is   
  
  
union_mc varchar2(200);   
  
  
begin   
  
  
for cur in (select mc from test where id=v_id) loop   
  
  
union_mc := union_mc||cur.mc;   
  
  
end loop;   
  
  
union_mc := rtrim(union_mc,1);   
  
  
return union_mc;   
  
  
end;   
  
  
/   
  
  
 
  
  
select id,link(id) from test group by id; 
 
 

-- Method 3:

/* Starting from Oracle 9i, developers can create user-defined aggregate functions. Apart from PL/SQL, they can also use any languages supported by Oralce, such as C ++ or Java) to create Aggregate functions. The TYPE header definition must containOdciaggresponinitialize,ODCIAggregateIterate,ODCIAggregateMergeAndOdciaggresponterminateThese four interface functions. */

/* InitializeThe function runs each field to be processed in the Data Group once. Naturally, I need to prepare a new list for each value, so I need to initialize the persistent variable.ListHere, the initialization value isNull. */

/* IterateThe function processes the returned rows, so it is actually used to create a list of returned values. Test firstListWhether it is null. If it is null, SetListDirectly set to the introducedValueValue; ifListIf the variable is not empty, add a comma to the list and insert it again.ValueValue,ListThe maximum number of characters allowed is 32767. */

/* TerminateThe function is executed after the field data of interest in each row in the Data Group is processed. In this function, I only need to simply return the list variable. */

/* MergeFunction, used to return the success mark. */

/* Create your own Aggregate functions to expand Oracle statistics and text processing capabilities. */

 
 

  
  
create or replace type t_cat as object   
  
  
(   
  
  
union_mc VARCHAR2(200),   
  
  
static function ODCIAggregateInitialize(sctx IN OUT t_cat) return number,   
  
  
member function ODCIAggregateIterate(self IN OUT t_cat,value IN varchar2) return number,   
  
  
member function ODCIAggregateTerminate(self IN t_cat,returnValue OUT varchar2, flags IN number) return number,   
  
  
member function ODCIAggregateMerge(self IN OUT t_cat,ctx2 IN t_cat) return number   
  
  
);   
  
  
 
  
  
create or replace type body t_cat is   
  
  
static function ODCIAggregateInitialize(sctx IN OUT t_cat )   
  
  
return number is   
  
  
begin   
  
  
sctx := t_cat('');   
  
  
return ODCIConst.Success;   
  
  
end;   
  
  
member function ODCIAggregateIterate(self IN OUT t_cat, value IN varchar2)   
  
  
return number is   
  
  
begin   
  
  
self.union_mc := self.union_mc || value;   
  
  
return ODCIConst.Success;   
  
  
end;   
  
  
member function ODCIAggregateTerminate(self IN t_cat, returnValue OUT varchar2, flags IN number) return number is   
  
  
begin   
  
  
returnValue := self.union_mc;   
  
  
return ODCIConst.Success;   
  
  
end;   
  
  
member function ODCIAggregateMerge(self IN OUT t_cat , ctx2 IN t_cat ) return number is   
  
  
begin   
  
  
return ODCIConst.Success;   
  
  
end;   
  
  
end;   
  
  
/ 
 
 

/* If your Oracle server is not configured to support parallel processing, remove the PARALLEL_ENABLE parameter */

Create or replace function catstr (v_mc varchar2) return varchar2 PARALLEL_ENABLE aggregate using t_cat;
/

Select id, catstr (mc) from test group by id;

-- Method 4:

-- Oracle9i or later

 
 

  
  
select id,ltrim(max(sys_connect_by_path(mc,';')),';') from(   
  
  
select id,mc,row_number() over(partition by id order by id) id1,   
  
  
row_number() over(order by id) + dense_rank() over(order by id) id2   
  
  
from test   
  
  
)   
  
  
start with id1=1 connect by prior id2 = id2 -1   
  
  
group by id order by id;  
 
 

Another method of Method 4

It is estimated that there are still a lot of similar writing methods. This is different from the previous one in the use of connect without the start withfilter function, and is implemented using level and first_value.

 
 

  
  
SELECT distinct id,ltrim(first_value(mc_add) over (partition by id order BY l DESC),';')   
  
  
from (   
  
  
SELECT id,LEVEL l,sys_connect_by_path(mc,';') mc_add from   
  
  
(   
  
  
select id||rownum rn,id||rownum-1 rn_small,id,mc from test   
  
  
)   
  
  
CONNECT BY PRIOR rn = rn_small   
  
  
)   
  
  
; 
 
 

Method 5:

 
 

  
  
select id,wm_concat(mc) from test group by id