Oracle column merge, oracle Column

Oracle column merge, oracle Column

In many cases, we need to use oracle column merge. oracle provides some methods to merge multiple rows of data in a column into one row.

 

1. Use WMSYS. WM_CONCAT before 10 GB
Wmsys. wm_concat separates the field values.
Select id, wm_concat (name) from tab_name group by id;

 

Ii. 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.

 

3. Use listparts after 11 GB
Select listparts (id, ',') within group (order by id) col_name from tab_name;

 

Iv. User-Defined Functions
Create or replace function getRow (table1 varchar2, ptdb1 varchar2) RETURN VARCHAR2 is
Result VARCHAR2 (1000 );
Begin
FOR cur IN (SELECT audit_code FROM sys_audit_column t2 WHERE table1 =
T2.table _ name and ptdb1 = t2.ptdb _ name) LOOP
RESULT: = RESULT | cur. audit_code | ',';
End loop;
RESULT: = rtrim (RESULT ,',');
Return (Result );
End getRow;
(Referenced from the network, I have not verified)

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.

Oracle single-column and multi-row merge

Perform the following tests based on your description:
-- Create a table
Create table kongxianji (a varchar2 (20), B varchar2 (20 ));

-- Insert data
Insert into kongxianji (a, B) VALUES ('A', 1 );
Insert into kongxianji (a, B) VALUES ('B', 2 );
Insert into kongxianji (a, B) VALUES ('C', 3 );
Insert into kongxianji (a, B) VALUES ('D', 4 );
Insert into kongxianji (a, B) VALUES ('E', 5 );
Insert into kongxianji (a, B) VALUES ('F', 6 );
Insert into kongxianji (a, B) VALUES ('G', 7 );
COMMIT;

-- Write statement blocks and execute
DECLARE
V_a varchar2 (20 );
V_ B varchar2 (50 );
CURSOR r_aa is select a. a FROM kongxianji a WHERE a. B IN (2, 3, 6, 7 );
BEGIN
OPEN r_aa;
LOOP
FETCH r_aa INTO v_a;
Exit when r_aa % NOTFOUND;
V_ B: = v_ B | v_a;
End loop;
CLOSE r_aa;
Dbms_output.put_line (v_ B );
END;

Note: The final result cannot be obtained through only one SQL statement. It must be obtained through loops.