Oracle pure SQL to merge multiple rows

To Merge multiple rows into one project, you must merge multiple rows into one project.
The table structure is as follows:
Name null type
--------------------------------------
N_sec_code not null char (6)
C_researcher_code not null varchar2 (20)

This table stores the ing data between "stock" and "researcher". Generally, for the same stock, there may be multiple researchers.
Perform follow-up research on it. Therefore, we have a requirement to query each stock and the corresponding researcher (Researcher Code Separated by commas ).
For example, the following data is available:
000297 chenpeng
000297 liusu
After merging, it must be displayed as follows:
000297 chenpeng, liusu

Many methods have been found on the internet, but generally the custom multi-line text merging function is compiled, or it has limitations on the number of supported columns.
Finally, we found the following clever method in Google. You don't need to add a function to the database. It can be done in a single SQL statement,
In addition, the scalability is good and there is no column restriction.

Code
Select N_sec_code, translate ( Ltrim ( Text , ' / ' ), ' */ ' , ' *, ' ) Researcherlist
From ( Select Row_number () Over (Partition By N_sec_code Order   By N_sec_code,
LVL Desc ) Rn,
N_sec_code, Text
From ( Select N_sec_code, Level LVL,
Sys_connect_by_path (c_researcher_code, ' / ' ) Text
From ( Select N_sec_code, c_researcher_code As C_researcher_code,
Row_number () Over (Partition By N_sec_code Order   By N_sec_code, c_researcher_code) x
From M_researcher_stock_rel
Order   By N_sec_code, c_researcher_code)
Connect By N_sec_code = Prior n_sec_code And X -   1   = Prior X ))
Where Rn =   1
Order   By N_sec_code;

The expected results are successfully displayed, and the multi-row data is successfully summarized into one row. We would like to share with you. For your own applications, you only need to set "n_sec_code" in SQL"
Replace "c_researcher_code" with the column to be merged text, and "m_researcher_stock_rel" with your table name.

SQL analysis:
1. Use "row_number () over (partition ......" Add group sequence numbers for the data rows summarized by "stock code"
2. "sys_connect_by_path" overlays the "researcher code" of different lines for each layer based on the adjacent relationship of the group sequence number.
3. Use the "stock code" to group groups in the group again, but sort the groups in reverse order in the second part to increase the adjusted level.
4. obtain all the results with the adjusted level of 1, that is, the required data row.

Simpler Method

Select X. code, X. name, wmsys. wm_concat (X. content) from t_xx X Group by X. code, X. the name project needs to merge multiple rows into one row.
The table structure is as follows:
Name null type
--------------------------------------
N_sec_code not null char (6)
C_researcher_code not null varchar2 (20)

This table stores the data on the ing between "stock" and "researcher". Generally, for the same stock, there may be multiple researchers
conducting follow-up research on it. Therefore, we have a requirement to query each stock and the corresponding researcher (separated by commas ).
for example, the following data is available:
000297 chenpeng
000297 liusu
after merging, the data must be displayed as:
000297 chenpeng, liusu

Many methods have been found on the internet, but generally the custom multi-line text merging function is compiled, or it has limitations on the number of supported columns.
Finally, we found the following clever method in Google. You don't need to add a function to the database. It can be done in a single SQL statement,
In addition, the scalability is good and there is no column restriction.

Code
Select N_sec_code, translate ( Ltrim ( Text , ' / ' ), ' */ ' , ' *, ' ) Researcherlist
From ( Select Row_number () Over (Partition By N_sec_code Order   By N_sec_code,
LVL Desc ) Rn,
N_sec_code, Text
From ( Select N_sec_code, Level LVL,
Sys_connect_by_path (c_researcher_code, ' / ' ) Text
From ( Select N_sec_code, c_researcher_code As C_researcher_code,
Row_number () Over (Partition By N_sec_code Order   By N_sec_code, c_researcher_code) x
From M_researcher_stock_rel
Order   By N_sec_code, c_researcher_code)
Connect By N_sec_code = Prior n_sec_code And X -   1   = Prior X ))
Where Rn =   1
Order   By N_sec_code;

The expected results are successfully displayed, and the multi-row data is successfully summarized into one row. We would like to share with you. For your own applications, you only need to set "n_sec_code" in SQL"
Replace "c_researcher_code" with the column to be merged text, and "m_researcher_stock_rel" with your table name.

SQL analysis:
1. Use "row_number () over (partition ......" Add group sequence numbers for the data rows summarized by "stock code"
2. "sys_connect_by_path" overlays the "researcher code" of different lines for each layer based on the adjacent relationship of the group sequence number.
3. Use the "stock code" to group groups in the group again, but sort the groups in reverse order in the second part to increase the adjusted level.
4. obtain all the results with the adjusted level of 1, that is, the required data row.

Simpler Method