Oracle row and column conversion (row merging and splitting)

Oracle row and column conversion (row merging and splitting)
Use wmsys. wm_concat to merge rows

In Oracle, you can use the wmsys. wm_concat function to convert multiple rows of data in a column to a comma-style line display.

This is in the previous http://www.bkjia.com/database/201204/128928.html

The built-in oracle function wmsys. wm_concat is also introduced.

Refer to this section

Case 1: Convert columns into rows. Display the names of all employees in one row

Select wmsys. wm_concat (NAME) from employee;

Result: user1, user2
"

Now there is a question: how to display it in turn? That is to say, there is a column value similar to: user1, user2. Now we want to split it into two rows for display.

The method is to use the function: regexp_substr

Use regexp_substr to split rows

This function depends on the name of a function that matches a regular expression. The specific format is as follows:

For example,

For user1 and user2, if you want to match the second one (user2), you can use the following SQL:

SELECT REGEXP_SUBSTR('user1,user2','[^,]+',1,2,'i') AS STR FROM DUAL; 

Result: user2

If you want to match all of them, use the following method:

SELECT REGEXP_SUBSTR('user1,user2','[^,]+',1,LEVEL,'i') AS STR FROM DUAL CONNECT BY LEVEL<3; 

User1

User2

It seems that the results have been achieved. However, the LEVEL here is a variable in many cases, which can be computed.

Perfect:

SELECT REGEXP_SUBSTR('user1,user2','[^,]+',1,LEVEL,'i') AS STR FROM DUAL CONNECT BY LEVEL<=LENGTH('user1,user2') - LENGTH(REGEXP_REPLACE('user1,user2', ',', ''))+1; 

select LENGTH('user1,user2') - LENGTH(REGEXP_REPLACE('user1,user2', ',', ''))+1 from dual;