(Original) Use sys_connect_by_path to convert multiple rows into single row strings

Use sys_connect_by_path to convert multiple rows into single-row strings.

Recently, several projects have the need to convert multiple rows into strings. Now we will write an example for your reference.
Create a table and insert data:
Create Table t_row_str (
ID number,
Col varchar2 (10 ));
Insert into t_row_str values (1, 'A ');
Insert into t_row_str values (1, 'B ');
Insert into t_row_str values (1, 'C ');
Insert into t_row_str values (2, 'A ');
Insert into t_row_str values (2, 'D ');
Insert into t_row_str values (2, 'E ');
Insert into t_row_str values (3, 'C ');
Commit;
Select * From t_row_str;

ID	Col
1	A
1	B
1	C
2	A
2	D
2	E
3	C

Sys_connetc_by_path is used to convert the STR field to a string.
Select T. ID, sys_connect_by_path (T. Col, ',') Str
From (select ID, Col, row_number () over (partition by id order by col) Rn
From t_row_str) T
Start with Rn = 1
Connect by Rn = prior rn + 1
And Id = prior ID;

ID	Str
1	,
1	, A, B
1	, A, B, c
2	,
2	, A, D
2	, A, D, E
3	, C

From the above results, we can see that the STR field has a comma and a few more lines. Now we can use substr to remove the first comma and then use Max to obtain the largest line:
The result is as follows:
Select T. ID, max (substr (sys_connect_by_path (T. Col, ','), 2) Str
From (select ID, Col, row_number () over (partition by id order by col) Rn
From t_row_str) T
Start with Rn = 1
Connect by Rn = prior rn + 1
And Id = prior ID
Group by T. ID;

ID	Str
1	A, B, c
2	A, D, E
3	C

You have successfully converted multiple rows into single-line strings. We hope this example will be useful to you.
The following is an example of code for accessing the entrepreneurial Park:
-- Display the positions and quantity of the same product as one line
Select v_product_code,
Max (substr (sys_connect_by_path (n_position_id | '/' | QT, ','), 2) Str
From (select T. v_product_code,
T. n_position_id,
Sum (T. n_qty) Qt,
Row_number () over (partition by T. v_product_code order by T. v_product_code) Rn
From tb_wh_pos_io_trace t
Group by v_product_code, T. n_position_id)
Start with Rn = 1
Connect by Rn = prior rn + 1
And v_product_code = prior v_product_code
Group by v_product_code;

V_product_code Str

---------------------------------------
518022000041 3/444, 4/111
518022000042 3/111