Implementation of oracle column merge: oracle column merge

Implementation of oracle column merge: oracle column merge

In many cases, we will use oracle column merge. oracle provides the following methods to implement column merge:

1. Before Oracle 10 Gb, WMSYS. WM_CONCAT was used:

Wmsys. wm_concat separates the field values.

select id,wm_concat(name) from tab_name group by id;

2. Use sys_connect_by_path

Sys_connect_by_path (field name, The Connection Symbol between two fields). Do not use a comma for the Connection Symbol here. oracle will report an error. If necessary, replace it with replace, the method is as follows REPLACE (field name, original character ,','). Before using this function, you must create a tree. Otherwise, it is useless.

Iii. listparts can be used after Oracle 11G

select listagg(id,',') within group (order by id) col_name from tab_name;

4. Use custom functions:

create or replace function getRow(table1 varchar2 , ptdb1 varchar2) RETURN VARCHAR2 isResult VARCHAR2(1000);beginFOR cur IN (SELECT audit_code FROM sys_audit_column t2 WHERE table1 =t2.table_name and ptdb1 = t2.ptdb_name) LOOPRESULT := RESULT||cur.audit_code||',';END LOOP;RESULT:=rtrim(RESULT,',');return(Result);end getRow;

Oracle merges two columns

Simple select a | B is definitely wrong. You must have the same field to merge it.

So you have to add a row number to the subquery of two tables and associate them with two rows.

In oracle statements, how does one combine the values in a column into a single value, separated by commas?

All versions of oracle can use select wm_concat (name) as name from user;
However, for oracle11g, select listparts (name, ',') within group (order by name) as name from user;
This method is more efficient and is recommended by the official team.