Data splitting and merging of Oracle tables

Oracle splits and merges table data in daily database operations. Let's take a look at how to implement it.
Assume that the table TEMP_TABLE (ID, NAME, CODE) has the following data: [SQL] select t. * from temp_table t; id name code www.2cto.com 1 A 102 B 203 C 304 A 405 A 506 A | B | C 60
We merge the data with NAME = A and use the Oracle function WM_CONCAT (). The SQL statement and result are as follows: [SQL] select t. name, to_char (wm_concat (t. code) from temp_table t where name = 'A' group by t. name; name codea, 50 because the result returned by WM_CONCAT is of the CLOB type, we can use the TO_CHAR function to convert it to a string, or use the REPLACE function to convert the result ', 'convert to other delimiters we need. Let's take A look at how to split A record into multiple records using the specified delimiter, using ID = 6 in as an example, NAME = A | B | C, we will split it into three records, the SQL statement and result are as follows:
[SQL] SELECT id, REGEXP_SUBSTR (name, '[^ |] +', 1, rn) B FROM temp_table, (SELECT ROWNUM rn FROM DUAL CONNECT BY ROWNUM <= 50) WHERE REGEXP_SUBSTR (name, '[^ |] +', 1, rn) is not null; id name www.2cto.com 1 A2 B3 C4 A5 A6 B6 C
Here, REGEXP_SUBSTR is the sharding function we will use. Split the field NAME with '|' as the separator. We can see the last three lines split the original NAME = A | B | C into three records.

Note that, after the FROM keyword (select rownum rn from dual connect by rownum <= 50), 50 is the maximum number of data records to be split, in this example, 3 is enough.