Oracle comma-delimited column switch implementation method _oracle

Now, if you give a permission number to retrieve a collection of users with this permission, you will need to match the given permission number in a comma-delimited number of permission numbers. If you use like to do, an inefficient, two matching is not accurate. So we used a comma-delimited list of ways to change careers. Currently, this method is only suitable for use in Oracle databases. This method requires only SQL statements to implement column careers.

An example of this method is given below:

Select A,b,c from
(with test as (select ' AAA ' A, ' BBB ' B, ' 1,2,3 ' C from dual)
Select A,b,substr (T.ca,instr (t.ca, ', ', 1, c.lv) + 1,instr (t.ca, ', ', 1, c.lv + 1)-(InStr (t.ca, ', ', 1, c.lv) + 1)) as C
From (select A,b, ', ' | | c | | ', ' as Ca,length (c | | ', ')-NVL (Length (REPLACE (c, ', '), 0) as CNT from test) T,
(Select level LV from dual CONNECT by level <=) C where c.lv <= t.cnt)

Execute the above code and the results are as follows:

The content "1,2,3" in the Analog data column C is turned into three lines, with the contents of the preceding two columns being accompanied by the past. In practical use, as long as the

Select ' AAA ' A, ' BBB ' B, ' 1,2,3 ' C from dual replaced with a field in the datasheet that actually needs to be converted, where the C field must be a field that preserves the comma-delimited content to be converted. None of the following can be changed. The number 100 in Level <= 100 represents the number of times a comma appears in the matching field's contents and can be changed on its own.