Oralce converts a column into multiple rows by Separator

1. Preface recently, due to project requirements, the employee's working group needs to be returned to the front-end, but the database stores the employee's working group Id in a field (with ldquo; comma

1. Preface recently, due to project requirements, the employee's working group needs to be returned to the front-end, but the database stores the employee's working group Id in a field (with ldquo; comma

1. Preface

Recently, due to project requirements, the employee's working group needs to be returned to the front-end. However, in the database, the employee's working group Id is included in a field (separated by commas ), this does not meet the requirements of the front-end. They need a row or a row of data. For example:

Database:

UserId, workgroup, 4, 5

Front-end requirements:

UserId, workgroup

2. Analysis ideas:

The general idea is as follows:

First, you must know the maximum number of groups each employee can have.

Second, create a temporary table related to "Number" and associate it with the number of groups above. In this way, "multiple" rows appear.

Finally, if multiple "rows" exist, the rest is to select the Group of each row. For example, if the first line is on the left of the first comma, and the second line is on the left of the second comma, It is pushed accordingly.

3. Implementation:

Based on the previous idea, we can achieve the following:

Step 1:

UserId, workgroups from dualuserId, workgroups from dual) workgroups tempgroups, length (workgroups), 0) AS groupcount FROM v_usergroups

PS: A comma is added before and after "workgroup" to facilitate later use.

Step 2:

PS: 5 here. Based on business needs, each employee can be divided into up to 5 groups. Of course, other values can also be written, but it must be greater than the "groupcount" obtained in the first step ".

After that, we can associate the two tables to see how the values are:

UserId, workgroups from dualuserId, workgroups from dual) (workgroups tempgroups, length (workgroups), 0) AS groupcount FROM v_usergroups) a, () B where B. lv <=. groupcount order by userid, lv

Userid tempgroups groupcount lv, 2, 4, 5, 3 3

Here, we are very close to the final result. You only need to perform a simple operation on "tempgroups" in the outer layer:

Step 3:

The main idea of this step is to cut the string. The first group should be the value between the first and second commas. The second group should be the value between the second and third commas, and the second one, which is actually using the field lv. That is:

Substr (tempgroups, instr (tempgroups, ',', 1, lv) + 1, instr (tempgroups, ',', 1, lv + 1)-(instr (tempgroups, ',', 1, lv) + 1 ))

The final SQL statement is as follows:

UserId, workgroups from dualuserId, workgroups from dual), 1, lv) + 1) from (workgroups tempgroups, length (workgroups), 0) AS groupcount FROM v_usergroups) a, () B where B. lv <=. groupcount order by userid, lv