Oracle pure SQL to merge multiple rows

Source: Internet
Author: User
If you encounter a requirement in the project, you need 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 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.

The method is clever and worth learning. :-)

Thank you @For octoberone and @ ericqliu, you can use the following methods in oracle10:
Select n_sec_code, wmsys. wm_concat (c_researcher_code) as result
From m_researcher_stock_rel
Group by n_sec_code

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.