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