[Oracle] Row and column transformations (line merge and Split)

implementing a row merge using Wmsys.wm_concat

In Oracle, multiple rows of data from one field are converted to a comma-style line display, which can be achieved using the function Wmsys.wm_concat.

This one in the previous article

Oracle Built-in function Wmsys.wm_concat useAlso introduced in the.

Refer to this paragraph "

Case 1: Column transformation row. Display the names of all employees in one line

Select Wmsys.wm_concat (NAME) from employee;

Results: User1,user2

”

Now there is a problem, in turn how to display? That is, there is a field value similar to: User1,user2, now you want to split it into two lines to display.

The method is to use the function: Regexp_substr

using REGEXP_SUBSTR to implement row splitting

This function, look at the name of a regular expression matching function. The specific format is as follows:

function Regexp_substr (String, pattern, position, occurrence, modifier)
__SRCSTR: A string that requires regular processing
__pattern: Regular expression for matching
__position: Starting position, starting with the first character of the regular expression match (default = 1)
__occurrence: Identifies the first few matching groups, which defaults to 1
__modifier: The pattern (' I ' is not case-sensitive for retrieval; ' C ' is case-sensitive for retrieval. The default is ' C '. ）

If you use an example,

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

Results: User2

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

Results:

User1

User2

It seems to have worked out. But the level here, in many cases, is a variable that can be calculated.

Perfect:

The number of calculations used here is as follows:

Select Length (' user1,user2 ')-Length (Regexp_replace (' User1,user2 ', ', ', ')) +1 from dual;

The principle is to use the length of the original string minus the length of the replacement split string is more than the last split string, plus 1 is more than the last element.

[Oracle] Row and column transformations (line merge and Split)