Oracle queries multiple rows of data to merge into one row of data

If it is Oracle 10g above a wm_concat function can be solved.

It's a bit of a hassle if you're under Oracle 9i.

The table structure is as follows:

NAME                            Null           Type---------------------------------   -----n_sec _code                 notNULLCHAR(6) C_researcher_code      not NULL VARCHAR2 ()

This table holds data on the correspondence between "stocks" and "Fellows", and in general, there may be more than one researcher for the same stock

The following research is carried out on it. So there is a requirement to query each stock and the corresponding researcher (researcher code, separated by commas).

For example, there are the following data:

000297 Chenpeng

000297 Liusu

The merge process needs to be displayed as:

000297 Chenpeng,liusu

Many methods have been looked up on the web, but generally they are written in custom multiline text merge functions or have limitations on the number of columns supported.

Finally in the English Google search for the following more ingenious method. Without adding function to the database, pure SQL is done at once,

And extensibility is good, there is no column limit.

SELECTN_sec_code, TRANSLATE (LTRIM(text,'/'),'*/','*,') Researcherlist from(SELECTRow_number () Over(PARTITION byN_sec_codeORDER  byN_sec_code, lvlDESC) RN, N_sec_code,text              from(SELECTN_sec_code, Levellvl, Sys_connect_by_path (C_researcher_code,'/')text                          from(SELECTN_sec_code, C_researcher_code asC_researcher_code, row_number () Over(PARTITION byN_sec_codeORDER  byn_sec_code,c_researcher_code) x fromM_researcher_stock_relORDER  byN_sec_code, C_researcher_code) a CONNECT byN_sec_code=PRIOR N_sec_code andX- 1 =PRIOR x)) WHERERn= 1ORDER  byN_sec_code;

The expected results are successful, and multiple rows of data are successfully aggregated to a single line, which is hereby shared with everyone. For your own application, just "N_sec_code" in SQL

Instead of the column you used to summarize, "C_researcher_code" is replaced with the column where you want to merge the text, and "M_researcher_stock_rel" is replaced with your table name, which is simple.

SQL Analysis:

1, the use of "row_number () over (PARTITION by ..." Add an intra-group ordinal to a data row that is summarized by the stock ticker

2, "Sys_connect_by_path" in the group within the number of adjacent relations, for each layer of different lines of "researcher Code" overlay

3, again using "Stock code" for group grouping, but in the second part of the hierarchy in reverse order, increase the adjusted level

4, take all adjusted after the level of 1 results, that is the required data row

The method is ingenious and worth learning.

The following methods can be used in ORACLE10:

SELECT  as result  from M_researcher_stock_rel GROUP  by N_sec_code

Another example:

 withtab as(Select ' on'No'SS'NameTenJiner,'Wheat'You fromDualUnion  AllSelect ' Geneva'No'DD'Name OneJiner,'Radish'You fromDualUnion  AllSelect 'Geneva'No'cc'Name9Jiner,'Soy'You fromDualUnion  AllSelect ' on'No'SS'NameTenJiner,'Cabbage'You fromdual)SELECTA.no, A.name, A.jiner,LTrim(MAX(Sys_connect_by_path (You,',')),',') You from(SELECTRow_number () Over(PARTITION bya.no, A.name, A.jinerORDER  byA.no, A.name, A.jiner) rn,a.*           fromtab a) Astart withRn= 1CONNECT byPRIOR RN=Rn- 1  anda.no=PRIOR a.no andA.name=PRIOR A.name andA.jiner=PRIOR A.jinerGROUP  byA.no, A.name, A.jiner

Oracle Wm_concat function for column changing, comma delimited

First let's take a look at this magical function wm_concat(column name), which separates the column values by the "," number, and displays them as a line, and then the example below to see how this magical function can be applied

Preparing test data

Sql> Create TableTest (ID Number, namevarchar2( -)); SQL> Insert  intoTestValues(1,'a'); SQL> Insert  intoTestValues(1,'b'); SQL> Insert  intoTestValues(1,'C'); SQL> Insert  intoTestValues(2,'D'); SQL> Insert  intoTestValues(2,'e'); SQL> Commit;

Effect 1: Row to Column

SQL>Select from test; Wm_concat (NAME)-------------------------------------------------------------------------  a,b,c,d,e

Effect 2: Replace the comma in the result with "|"

Sql> Select Replace(Wm_concat (name),',','|') fromtest;REPLACE(Wm_concat (NAME),',','|')-----------------------------------------------------------------------a|B|C|D|E

Effect 3: Group by ID merge name

SQL>Selectfromgroup by id;id NAME---------- ------------------------------1  a,b,c2 d,e

Lazy extension Usage:

Case: I want to write a view similar to "Create or Replace view as Select field 1,... field from TableName ", the base table has more than 50 fields, if it is too cumbersome to write by hand, is there any easy way? Sure, look at me. If you apply wm_concat to make this requirement simple

Sql> Select 'Create or Replace view as select'||Wm_concat (column_name)|| 'From Dept' fromUser_tab_columnswheretable_name='DEPT';'Createorreplaceviewasselect'||Wm_concat (column_name)||'fromdept'--------------------------------------------------------------------------------Create or Replace View  as SelectDeptno,dname,loc fromDept

Oracle queries multiple rows of data to merge into one row of data