oracle字符串转成多列查询

44a7jdi3a2

1,将如下结果
id privilege_id
1  123,234,43532
2  2342,4353,24324
3  3453,463,663
转换成
id privilege_ids
1  123
1  234
1  43532
2  2342
2  4353
2  24324
3  3453
3  463
3  663

2，sql如下
select a.id,a.title,substr(privilege_id,instr(a.privilege_id,',',1,b.num-1) + 1,(instr(a.privilege_id,',',1,b.num)-instr(a.privilege_id,',',1,b.num-1))-1) privilege_ids
  from (select id,title,','||ltrim(rtrim(privilege_id,','),',')||',' privilege_id from notice_content_t) a,
       (select rownum+1 num from dual connect by rownum <=(select max(length(privilege_id)) num from  notice_content_t)) b
where length(privilege_id)-length(replace(privilege_id,',','')) >= b.num



扩展可参考：http://www.itpub.net/thread-1017026-1-1.html