Several ways to combine/connect/aggregate strings for Oracle multiple-line Records _oracle

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

Sql> select * from test;


COUNTRY City
-------------------- --------------------
Taipei, China
Hong Kong, China
Shanghai, China
Tokyo, Japan
Osaka, Japan
The following result sets are required:
------- --------------------
China Taipei, Hong Kong, Shanghai
Tokyo, Japan, Osaka
The real thing is to implement an aggregation function on the character, and I wonder why Oracle did not provide an official aggregate function to implement it:
The following is an analysis of several frequently mentioned solutions (with a benchmark of highest ★★★★★):
1. The collection field scope is small and fixed type flexibility ★ Performance ★★★★ Difficulty ★
The principle of this approach is that you already know that there are several values in the City field, and not too much, if too many of this SQL will be quite long. See 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 Taipei, Hong Kong, Shanghai,
Tokyo, Japan, Osaka,
As soon as you look at it, you'll get the idea (if you don't understand, Cram Max Decode and grouping). This is the most stupid way to do it, but for some applications, perhaps the most effective way is it.
2. Fixed table fixed field function method Flexibility ★ ★★★★ Difficulty ★
This method must know in advance which table, that is, a table to write a function, but method 1 of a value is more convenient. In most applications, there is no need for a large number of such merged strings. End of the nonsense, look at the following:
Define a function
Create or Replace function str_list (str_in in VARCHAR2)--category field
return VARCHAR2
Is
Str_list varchar2 (4000) default null;--concatenated string
Str VARCHAR2 (a) 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
Use:
Sql> Select DISTINCT (t.country), List_func1 (t.country) from Test T;

COUNTRY list_func1 (T.country)
-------------------- ----------------
China Taipei, Hong Kong, Shanghai
Tokyo, Japan, Osaka

Sql> Select T.country,str_list (t.country) from Test T GROUP by T.country;


COUNTRY str_list (T.country)
-------------------- -----------------------
China Taipei, Hong Kong, Shanghai
Tokyo, Japan, Osaka
At this time, the use of grouping and uniqueness can meet the requirements. Its principle is that, according to the Unique grouping field country, in the function again query the field corresponding to all the merged columns, use Pl/sql to merge them with the output.
3. Flexible table Function Method Flexibility ★★★ Performance ★★★ Difficulty ★★★
This method is based on Method 2, using dynamic SQL, the table name and field names are passed in, so as to achieve 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 Taipei, Hong Kong, Shanghai
Tokyo, Japan, Osaka
4. One SQL method Flexibility ★★★★ Performance ★ Hard ★★★★
One of the laws of SQL was proposed by a master, we used to be happy at some point. A SQL method, but the master's meaning seems to be misinterpreted, a lot of poor performance, poor readability, flexibility of the poor SQL is the product of this principle, the so-called Tiger painting is not a dog-like. However, to solve the problem is always the first principle, here is a more representative of a SQL method.
SELECT Country,max (substr (city,2)) City
From
(SELECT Country,sys_connect_by_path 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;
The following step-by-step parsing, with 4 from, has 4 result set operations.
Step 1, add serial number 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
5 I


COUNTRY City RN
-------------------- -------------------- ----------
Osaka, Japan 1
Tokyo 2, Japan
Shanghai 1, China
Taipei 2, China
Hong Kong, China 3
Step 2 creating 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 1st Ben 0
Japan Tokyo Japan 2nd 1
SHANGHAI China 1 China 0
China Taipei China 2 China 1
China, Hong Kong, China 3 China 2
Step 3 generating result sets 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 and rfather like '%0 '
Osaka, Japan
Japan, Osaka, Tokyo
China, Shanghai
China, Shanghai, Taipei
China, Shanghai, Taipei, Hong Kong
Step 4 Final steps to filter the result collection
Sql> SELECT Country,max (substr (city,2)) City
2 from
3 (SELECT Country,sys_connect_by_path 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 ')
GROUP by country;


COUNTRY City
-------------------- -------
Shanghai, China, Taipei, Hong Kong
Tokyo, Osaka, Japan


is to be, 7 crooked 8 make, finally still get out, hehe. PS: (Logically, yes.) But write more cumbersome, can be simplified!
5. Custom aggregate function Flexibility ★★★★★ performance ★★★★★ Difficulty ★★★★★
The final method is the way I think "kingly", a custom aggregate function.
As I said at the beginning of this, why does Oracle have no such aggregate function? I don't know, but Oracle provides an API for aggregating functions that makes it easy for me to define aggregate functions myself.
You can look at Oracle Data Catridge Guide This document in detail. The connection is as follows:
Http://www.oracle.com.cn/other/9ionlinedoc/appdev.920/a96595/toc.htm
A simple example is given below:
Sql> SELECT T.country,strcat (t.city) from Test T GROUP by T.country;


COUNTRY STRCAT (t.city)
-------------------- ------------------
Tokyo, Japan, Osaka
China Taipei, Hong Kong, Shanghai
It's as easy and efficient as the official function.
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 Odciaggregateinitialize (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 Odciaggr Egateterminate (self in Out strcat_type,returnvalue out
Varchar2,flags in number)
6. To be explored ...

PS: Under Oracle 10g, you can use the following system functions:
Select Id,wmsys. Wm_concat (OID) OID
From table1
GROUP BY ID
Summing up, merging strings there are more ways to hope that we can explore, the purpose of this article is mainly to introduce, if there are new discoveries I will continue to update the method. The point to note is that this article uses VARCHAR2 as an example, so the length is limited and the Oracle version affects the implementation of the method.