表中记录如下:
select * from t;
N A
-- ----------
1 a,b
2 a,d,f
希望根据a列中的逗号,将表中记录折分为多行,输出结果如下:
N A
-- --
1 a
1 b
2 a
2 d
2 f
实现方法如下所示:
select * from t;
N A
-- ----------
1 a,b
2 a,d,f
希望根据a列中的逗号,将表中记录折分为多行,输出结果如下:
N A
-- --
1 a
1 b
2 a
2 d
2 f
实现方法如下所示:
点击(此处)折叠或打开
- SQL> with t as
- 2 (select 1 n, 'a,b,aaa' a
- 3 from dual
- 4 union all
- 5 select 2, 'aa,dc,f'
- 6 from dual
- 7 union all
- 8 select 3, 'aa,a,a,a'
- 9 from dual
- 10 union all
- 11 select 6, 'aaaa,ttttttt,55555'
- 12 from dual)
- 13 select distinct level, n, regexp_substr(a, '[^,]+', 1, le
- 14 from t
- 15 connect by level <= regexp_count(a, ',') + 1
- 16 AND n = PRIOR n
- 17 AND PRIOR SYS_GUID() IS NOT NULL
- 18 order by n, level;
-
- LEVEL N REGEXP_SUBSTR(A,'[^,]+',1,LEVEL)
- ---------- ---------- ------------------------------------
- 1 1 a
- 2 1 b
- 3 1 aaa
- 1 2 aa
- 2 2 dc
- 3 2 f
- 1 3 aa
- 2 3 a
- 3 3 a
- 4 3 a
- 1 6 aaaa
-
- LEVEL N REGEXP_SUBSTR(A,'[^,]+',1,LEVEL)
- ---------- ---------- ------------------------------------
- 2 6 ttttttt
- 3 6 55555
-
- 已选择13行。
-
- SQL>
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21251711/viewspace-1757788/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/21251711/viewspace-1757788/