Oracle 一行拆分为多行

测试数据：

[sql] view plaincopy在CODE上查看代码片派生到我的代码片
  1. CREATE TABLE t (str VARCHAR2(30));  
  2. INSERT INTO t VALUES ( 'X,Y,Z' );  
  3. INSERT INTO t VALUES ( 'XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG' );  
  4.   
  5. commit;  


--1、multiset 8i之后都支持

[sql] view plaincopy在CODE上查看代码片派生到我的代码片
  1. SQL> CREATE OR REPLACE TYPE number_ntt AS TABLE OF NUMBER;  
  2.   2  /  
  3.    
  4. Type created  
  5. SQL> col value for a20  
  6. SQL>   
  7. SQL> with ilv as  
  8.   2   (select str || ',' as str,  
  9.   3           (length(str) - length(replace(str, ','))) + 1 as no_of_elements  
  10.   4      from t)  
  11.   5  select a.str, regexp_substr(a.str, '[^,]+', 1, b.column_value) value  
  12.   6    from ilv a, table(cast(multiset (select rownum rn  
  13.   7                       from dual  
  14.   8                     connect by rownum <= a.no_of_elements) as number_ntt)) b;  
  15.    
  16. STR                             VALUE  
  17. ------------------------------- --------------------  
  18. X,Y,Z,                          X  
  19. X,Y,Z,                          Y  
  20. X,Y,Z,                          Z  
  21. XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG, XXX  
  22. XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG, Y  
  23. XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG, ZZ  
  24. XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG, AAAAA  
  25. XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG, B  
  26. XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG, CCC  
  27. XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG, D  
  28. XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG, E  
  29. XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG, F  
  30. XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG, GGG  
  31.    
  32. 13 rows selected  
  33.    
  34. SQL>   


2、自关联connect by 10g之后

[sql] view plaincopy在CODE上查看代码片派生到我的代码片
  1. SQL> select str,  
  2.   2         regexp_substr(str, '[^,]+', 1, level) value  
  3.   3    from t  
  4.   4  connect by  
  5.   5   str = prior str  
  6.   6   and instr(str||',', ',', 1, level) > 0  
  7.   7   and prior dbms_random.value is not null;  
  8.    
  9. STR                            VALUE  
  10. ------------------------------ --------------------  
  11. X,Y,Z                          X  
  12. X,Y,Z                          Y  
  13. X,Y,Z                          Z  
  14. XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG XXX  
  15. XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG Y  
  16. XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG ZZ  
  17. XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG AAAAA  
  18. XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG B  
  19. XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG CCC  
  20. XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG D  
  21. XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG E  
  22. XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG F  
  23. XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG GGG  
  24.    
  25. 13 rows selected  
  26.    
  27. SQL>   


3、自关联，非CONNECT BY方式

[sql] view plaincopy在CODE上查看代码片派生到我的代码片
  1. SQL> with tmp as (  
  2.   2  select t.*,  
  3.   3         length(str)-length(regexp_replace(str, ',', ''))+1 len  
  4.   4    from t  
  5.   5  )  
  6.   6  select a.*, regexp_substr(str, '[^,]+', 1, rn) value  
  7.   7    from tmp a, (select rownum rn from dual connect by level <= (select max(len) from tmp x)) b  
  8.   8   where a.len>=b.rn  
  9.   9   order by 1;  
  10.    
  11. STR                                   LEN VALUE  
  12. ------------------------------ ---------- --------------------  
  13. X,Y,Z                                   3 Y  
  14. X,Y,Z                                   3 Z  
  15. X,Y,Z                                   3 X  
  16. XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG         10 GGG  
  17. XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG         10 B  
  18. XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG         10 CCC  
  19. XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG         10 D  
  20. XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG         10 E  
  21. XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG         10 F  
  22. XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG         10 ZZ  
  23. XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG         10 Y  
  24. XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG         10 XXX  
  25. XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG         10 AAAAA  
  26.    
  27. 13 rows selected  
  28.    
  29. SQL>   


4、Model子句，复杂度有点小高

[sql] view plaincopy在CODE上查看代码片派生到我的代码片
  1. SQL> col single_element for a15  
  2. SQL>   
  3. SQL> with ilv as  
  4.   2   (select str as orig_str,  
  5.   3           ',' || str || ',' as mod_str,  
  6.   4           1 as start_pos,  
  7.   5           length(str) as end_pos,  
  8.   6           (length(str) - length(replace(str, ','))) + 1 as element_count,  
  9.   7           0 as element_no,  
  10.   8           rownum as rn  
  11.   9      from t)  
  12.  10  select orig_str as original_string,  
  13.  11         substr(mod_str, start_pos, end_pos - start_pos) as single_element,  
  14.  12         element_no,  
  15.  13         element_count  
  16.  14    from (select *  
  17.  15            from ilv  
  18.  16           model partition by(rn, orig_str, mod_str)  
  19.  17                 dimension by(element_no)  
  20.  18                 measures(start_pos, end_pos, element_count)  
  21.  19                 rules iterate(2000)  
  22.  20                    until(iteration_number + 1 = element_count[0])(  
  23.  21                        start_pos[iteration_number + 1] = instr(cv(mod_str), ',', 1, cv(element_no)) + 1,  
  24.  22                        end_pos[iteration_number + 1] = instr(cv(mod_str), ',', 1, cv(element_no) + 1)  
  25.  23                    )  
  26.  24          )  
  27.  25   where element_no != 0  
  28.  26   order by mod_str, element_no;  
  29.    
  30. ORIGINAL_STRING                SINGLE_ELEMENT  ELEMENT_NO ELEMENT_COUNT  
  31. ------------------------------ --------------- ---------- -------------  
  32. X,Y,Z                          X                        1   
  33. X,Y,Z                          Y                        2   
  34. X,Y,Z                          Z                        3   
  35. XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG XXX                      1   
  36. XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG Y                        2   
  37. XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG ZZ                       3   
  38. XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG AAAAA                    4   
  39. XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG B                        5   
  40. XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG CCC                      6   
  41. XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG D                        7   
  42. XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG E                        8   
  43. XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG F                        9   
  44. XXX,Y,ZZ,AAAAA,B,CCC,D,E,F,GGG GGG                     10   
  45.    
  46. 13 rows selected  
  47.    
  48. SQL>   


其他可以编写自定义函数进行拆分

posted @ 2015-12-18 15:14  老白条  阅读(2911)  评论(0)  编辑  收藏  举报