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, multiple researchers may follow up on it. Therefore, we have a requirement to query each stock and the corresponding researcher (separated by commas ). For example, the following data is displayed: 000297 chenpeng 000297 after liusu is merged and processed: 000297 chenpeng. Many methods are found on the liusu website. However, the custom multiline text Merge function is usually compiled, or it has limitations on the number of supported columns. Finally, we found the following clever method in google. You do not need to add a function to the database. It is done in a single SQL statement and has good scalability and no column restrictions. 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, transform (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) a 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. In your own applications, you only need to replace "n_sec_code" in the SQL statement with your column for summary. "c_researcher_code" is replaced with the column for text merging, replace "m_researcher_stock_rel" with your table name, which is so simple. SQL analysis: 1. Use "ROW_NUMBER () OVER (PARTITION ......" Add group sequence numbers 2 and SYS_CONNECT_BY_PATH to the data rows that are summarized by the stock code, stack the "researcher code" of different lines for each layer. 3. Use the "stock code" to group groups in the group again, but sort them in reverse order, increase the adjusted level 4, take all the adjusted level 1 results, that is, the required data row method is very clever, it is worth learning. :-) Thanks to @ OctoberOne and @ ericqliu, the following methods can be used in ORACLE10: SELECT n_sec_code, wmsys. wm_concat (c_researcher_code) as resultFROM m_researcher_stock_relGROUP BY n_sec_code