Several Methods for Oracle multi-row record merging, connection, and string Aggregation

What is a multiline string (connection string), for example:
SQL> desc test;
Name Type Nullable Default Comments
------------------------------------------
COUNTRY VARCHAR2 (20) Y
CITY VARCHAR2 (20) Y

SQL> select * from test;


COUNTRY CITY
----------------------------------------
Taipei, China
Hong Kong, China
Shanghai, China
Tokyo, Japan
Osaka, Japan
The following result set is required:
---------------------------
China's Taipei, Hong Kong, Shanghai
Tokyo, Japan, and Osaka
Actually, it is to implement an aggregation function for characters. I wonder why Oracle does not provide an official aggregate function to implement it :)
The following is an analysis of several frequently-mentioned solutions (with the highest evaluation standard ).★★★★★):
1. The set fields have a small range and fixed flexibility.★Performance★★★★Difficulty★
The principle of this method is that you already know several values of the CITY field, and there are not too many values. If there are too many values, this SQL statement will be quite long .. Example:
SQL> select t. country,
2 MAX (decode (t. city, 'taipei ', t. city |', ', NULL) |
3 MAX (decode (t. city, 'Hong Kong ', t. city |', ', NULL) |
4 MAX (decode (t. city, 'shanghai', t. city | ',', NULL) |
5 MAX (decode (t. city, 'Tokyo ', t. city |', ', NULL) |
6 MAX (decode (t. city, 'osaka ', t. city |', ', NULL ))
7 from test t group by t. country
8/


Country max (DECODE (T. CITY, 'taipei ', T. CIT
--------------------------------------------------
China's Taipei, Hong Kong, Shanghai,
Tokyo, Osaka, Japan,
You can understand it at a Glance (if you don't understand it, study max decode and grouping well ). This method is the most stupid method, but for some applications, the most effective method may be it.
2. Fixed table fixed field Function Method flexibility★★Performance★★★★Difficulty★★
This method must know in advance which table it is, that is, a table has to write a function, but a value of method 1 is much more convenient. In most applications, there is no need to merge a large number of such strings. Go to the following link:
Define a function
Create or replace function str_list (str_in in varchar2) -- Category field
Return varchar2
Is
Str_list varchar2 (4000) default null; -- connected string
Str varchar2 (20) default null; -- Connection Symbol
Begin
For x in (select TEST. CITY from TEST where TEST. COUNTRY = str_in) loop
Str_list: = str_list | str | to_char (x. city );
Str: = ',';
End loop;
Return str_list;
End;
Usage:
SQL> select DISTINCT (T. country), list_func1 (t. country) from test t;

COUNTRY LIST_FUNC1 (T. COUNTRY)
------------------------------------
China's Taipei, Hong Kong, Shanghai
Tokyo, Japan, and Osaka

SQL> select t. country, str_list (t. country) from test t GROUP BY t. country;


COUNTRY STR_LIST (T. COUNTRY)
-------------------------------------------
China's Taipei, Hong Kong, Shanghai
Tokyo, Japan, and Osaka
In this case, grouping and uniqueness can meet the requirements. The principle is that, based on the unique grouping field country, all merged columns corresponding to this field are queried again in the function, and merged and output by PL/SQL.
3. Flexible Table Function Method flexibility★★★Performance★★★Difficulty★★★
This method is based on method 2 and uses dynamic SQL to pass in the table name and field name for flexible purposes.
Create or replace function str_list2 (key_name in varchar2,
Key in varchar2,
Coname in varchar2,
Tname in varchar2)
Return varchar2
As
Type rc is ref cursor;
Str varchar2 (4000 );
Sep varchar2 (2 );
Val varchar2 (4000 );
Cur rc;
Begin
Open cur for 'select' | coname |'
From '| tname |'
Where '| key_name |' =: x'
Using key;
Loop
Fetch cur into val;
Exit when cur % notfound;
Str: = str | sep | val;
Sep: = ',';
End loop;
Close cur;
Return str;
End;
SQL> select test. country,
2 str_list2 ('country', test. COUNTRY, 'city', 'test') emplist
3 from test
4 group by test. country
5/


COUNTRY EMPLIST
-------------------------------------
China's Taipei, Hong Kong, Shanghai
Tokyo, Japan, and Osaka
4. Flexibility of one SQL statement★★★★Performance★★Difficulty★★★★
One SQL rule was proposed by a master. in a certain period of time, everyone was happy to seek a variety of SQL methods. However, the meaning of the Master seems to be misinterpreted, and many of them have poor performance, poor readability and poor flexibility are the results of this principle. However, solving the problem is always the first principle. Here is a representative SQL method.
SELECT country, max (substr (city, 2) city
FROM
(SELECT country, sys_connect_by_path (city, ',') city
FROM
(SELECT country, city, country | rn rchild, country | (rn-1) rfather
FROM
(SELECT test. country, test. city, row_number () over (partition by test. country order by test. city) rn FROM test ))
Connect by prior rchild = rfather start with rfather LIKE '% 0 ')
Group by country;
In the following step-by-step parsing, if there are 4 FROM, there will be 4 result set operations.
Step 1 Add the SN rn to the record
SQL> SELECT test. country, test. city, row_number () over (PARTITION BY test. country ORDER BY test. city) rn
2 FROM test
3/


COUNTRY CITY RN
--------------------------------------------------
Osaka 1, Japan
Tokyo, Japan 2
China Shanghai 1
China Taipei 2
Hong Kong, China 3
Step 2 create a child node parent node
SQL> SELECT country, city, country | rn rchild, country | (rn-1) rfather
2 FROM
3 (SELECT test. country, test. city, row_number () over (partition by test. country order by test. city) rn
4 FROM test)
5/
Japan Osaka Japan 1 Japan 0
Tokyo Japan 2 Japan 1
China shanghai china 1 China 0
China Taipei China 2 China 1
China Hong Kong China 3 China 2
Step 3 generate a result set using sys_connect_by_path
SELECT country, sys_connect_by_path (city, ',') city
FROM
(SELECT country, city, country | rn rchild, country | (rn-1) rfather
FROM
(SELECT test. country, test. city, row_number () over (partition by test. country order by test. city) rn FROM test) connect by prior rchild = rfather start with rfather LIKE '% 0'
Japan and Osaka
Japan, Osaka, Tokyo
China, Shanghai
China, Shanghai, and Taipei
China, Shanghai, Taipei, Hong Kong
Step 4 Final step: filter the result set
SQL> SELECT country, max (substr (city, 2) city
2 FROM
3 (SELECT country, sys_connect_by_path (city, ',') city
4 FROM
5 (SELECT country, city, country | rn rchild, country | (rn-1) rfather
6 FROM
7 (SELECT test. country, test. city, row_number () over (partition by test. country order by test. city) rn
8 FROM test ))
9 connect by prior rchild = rfather start with rfather LIKE '% 0 ')
10 group by country;


COUNTRY CITY
---------------------------
China Shanghai, Taipei, Hong Kong
Osaka, Tokyo


It can be said that, in, we finally got it, huh, huh. PS: (logically, it is correct. But it is complicated to write and can be simplified !)
5. Flexibility of custom Aggregate functions★★★★★Performance★★★★★Difficulty★★★★★
The last method is the "Wang Dao" method, which is used to customize Aggregate functions.
In this article, why does oracle not have such aggregate functions? I don't know, But oracle provides an aggregate function API that allows me to easily define Aggregate functions by myself.
For more information, see the Oracle Data Catridge guide document. The connection is as follows:
Http://www.oracle.com.cn/other/9ionlinedoc/appdev.920/a96595/toc.htm
The following is a simple example:
SQL> SELECT t. country, strcat (t. city) FROM test t GROUP BY t. country;


Country strcat (T. CITY)
--------------------------------------
Tokyo, Japan, and Osaka
China's Taipei, Hong Kong, Shanghai
Simple, just as convenient and efficient as the official functions.
Function:
Create or replace function strcat (input varchar2)
RETURN varchar2
PARALLEL_ENABLE aggregate using strcat_type;
TYPE:
Create or replace type strcat_type as object (
Cat_string varchar2 (4000 ),
Static function odciaggresponinitialize (cs_ctx In Out strcat_type) return number,
Member function ODCIAggregateIterate (self In Out strcat_type, value in varchar2) return number,
Member function ODCIAggregateMerge (self In Out strcat_type, ctx2 In Out strcat_type) return number, member function odciaggresponterminate (self In Out strcat_type, returnValue Out
Varchar2, flags in number) return number)
6. Waiting for exploration...

PS: in oracle 10 Gb, you can use the following system functions:
Select id, WMSYS. WM_CONCAT (oid) oid
From table1
Group by id
To sum up, there are more methods to merge strings. We hope you can explore them. The purpose of this article is to introduce others. If there are new discoveries, I will continue to update the methods. Note that varchar2 is used as an example in this article, so the length is limited. The oracle version also affects the implementation of the method.