Oracle Aggregate functions solve the problem of clustered connection strings

Requirements:
Given data table: wm_test
Code name
1
1 B
1 c
2 In progress
2 countries
2 persons

Required results (the separator can be input by parameters ):
Code name
1 a, B, c
2 middle, country, people

This problem can be solved using the wmsys. wm_concat function of Oracle:
Select wt. Code, wm_concat (wt. Name) names from wm_test wt group by wt. Code;

However, there are three problems:
1. wmsys. wm_concat is only available in 10 GB and cannot be used in previous versions.
2. wmsys. wm_concat is an internal Oracle function and has not been released. That is to say, you can use it. However, Oracle is not responsible for any problems. The most obvious difference is that the Oracle version is upgraded from 10.2.0.4 to 10.2.0.5, but it is only a small version change that can cause problems in your system.

Solution:
1. Upgrade to Oracle 11g Release 2. This version introduces the listparts function, which makes it easy to aggregate the connection string and allows you to use the field sequence in the specified connection string.
2. Replace wmsys. wm_concat with your own defined aggregate function

The Code is as follows:

-- 1. create a test table and data: Create Table wm_test (Code integer, name varchar2 (20 byte); insert into wm_test (Code, name) values (1, 'A '); insert into wm_test (Code, name) values (1, 'B'); insert into wm_test (Code, name) values (1, 'C'); insert into wm_test (code, name) values (2, '中'); insert into wm_test (Code, name) values (2, 'country '); insert into wm_test (Code, name) values (2, 'people'); Commit; -- 2. create or replace type concatobj as object (fieldvalue varchar2 (4000), separator varchar2 (100 )) /create or replace type type_wm_concat as object (l_join_str varchar2 (32767 byte), -- connected string l_flag varchar2 (100 byte), -- separator, by default, you can define the static function odciaggresponinitialize -- initialization (sCTX in out type_wm_concat) return number, member function odciaggregateiterate -- iterator in the body to process each row of data) return number, member function odciaggresponterminate -- post-iteration processing code (self in out type_wm_concat, return_v out varchar2, flags in number) return number, member function merge -- result Merge (self in out type_wm_concat, ctx2 in type_wm_concat) return number);/create or replace type body type_wm_concatis static function initialize -- initialization (sCTX in out type_wm_concat) return number is begin sCTX: = type_wm_concat (null, null ); return odciconst. success; end odciaggresponinitialize; member function odciaggregateiterate -- iterator that processes each row of data (self in out type_wm_concat, value in concatobj) return number is begin if self. rochelle join_str is not null and value. fieldvalue is not null then self. rochelle join_str: = self. rochelle join_str | self. rochelle flag | value. fieldvalue; elsif value. fieldvalue is not null then self. rochelle join_str: = value. fieldvalue; self. rochelle flag: = value. separator; end if; return odciconst. success; end; member function odciaggresponterminate -- post-iteration code (self in out type_wm_concat, return_v out varchar2, flags in number) return number is begin return_v: = self. rochelle join_str; return odciconst. success; end; member function odciaggregatemerge (self in out type_wm_concat, ctx2 in type_wm_concat) return number is begin if ctx2.l _ join_str is not null and self. rochelle join_str is not null then self. rochelle join_str: = self. rochelle join_str | self. rochelle flag | ctx2.l _ join_str; elsif ctx2.l _ join_str is not null then self. rochelle join_str: = ctx2.l _ join_str; end if; return odciconst. success; end;/-- 3. encapsulated as a common SQL function: Create or replace function my_wm_concat (pi_str concatobj) return varchar2 parallel_enable aggregate using type_wm_concat;/-- 4. test: Select wt. code, my_wm_concat (concatobj (wt. name, '| + =') names from wm_test wtgroup by wt. code;

Code name
1 A | + = B | + = C
2 | + = Country | + = person