oracle将一行数据拆分成多行,Oracle 一行拆分为多行

SQL> col single_element for a15

SQL>

SQL> with ilv as

2   (select str as orig_str,

3           ‘,‘ || str || ‘,‘ as mod_str,

4           1 as start_pos,

5           length(str) as end_pos,

6           (length(str) - length(replace(str, ‘,‘))) + 1 as element_count,

7           0 as element_no,

8           rownum as rn

9      from t)

10  select orig_str as original_string,

11         substr(mod_str, start_pos, end_pos - start_pos) as single_element,

12         element_no,

13         element_count

14    from (select *

15            from ilv

16           model partition by(rn, orig_str, mod_str)

17                 dimension by(element_no)

18                 measures(start_pos, end_pos, element_count)

19                 rules iterate(2000)

20                    until(iteration_number + 1 = element_count[0])(

21                        start_pos[iteration_number + 1] = instr(cv(mod_str), ‘,‘, 1, cv(element_no)) + 1,

22                        end_pos[iteration_number + 1] = instr(cv(mod_str), ‘,‘, 1, cv(element_no) + 1)

23                    )

24          )

25   where element_no != 0

26   order by mod_str, element_no;

ORIGINAL_STRING                SINGLE_ELEMENT  ELEMENT_NO ELEMENT_COUNT

------------------------------ --------------- ---------- -------------

X,Y,Z                          X                        1

X,Y,Z                          Y                        2

X,Y,Z                          Z                        3

XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG XXX                      1

XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG Y                        2

XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG ZZ                       3

XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG AAAAA                    4

XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG B                        5

XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG CCC                      6

XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG D                        7

XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG E                        8

XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG F                        9

XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG GGG                     10

13 rows selected

SQL>