Oracle Query Merge Columns

In Oracle queries, there are times when you want to merge multiple columns into a single row, with the following methods:

1. Decode function

The syntax for the decode function is:

　　 Decode (condition, value 1, return value 1, value 2, return value 2,... Value N, return value N, default value)

or decode (condition, value 1, return value 1, return value 2,... Value N, return value N, default value)

It is defined as follows:

IF condition = value 1 then return (translation value 1) elsif condition = value 2 then return (translation value 2) ... elsif condition = value n then return (translated value n) ELSE return (default) END IF

　

For example: Decode (field or Field operation, value 1, value 2, value 3)
The result of this function is that when the value of the field or Field operation is equal to the value 1 o'clock, the function returns the value 2, otherwise the value 3
Of course, a value of 1, a value of 2, a value of 3 can also be an expression, this function makes some SQL statements a lot simpler

2. wmsys.wm_concat function

Merge a column with the syntax: wm_concat (column name)

Replace the merged comma (default) with another symbol, such as the | number, replace (wm_concat (column name), ', ', ' | ')

3. | | Connection symbols

The syntax is: Select Col1,col2| | ' "' | | col3| | ' As A, col4 from table1 where ' "' is a self-added symbol that can be replaced as needed

4. Custom SQL statements

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)  a                   connect by n_sec_code = prior n_sec_code and x - 1 =  prior x))    WHERE rn = 1ORDER BY n_sec_code;

　　
Just replace "N_sec_code" in SQL with the column you are summarizing, "C_researcher_code" with the column you want to merge the text from, "M_researcher_stock_rel" to your table name

Reference article:

Http://www.cnblogs.com/heekui/archive/2009/07/30/1535516.html
http://blog.csdn.net/catoop/article/details/8124561

Oracle Query Merge Columns