ORACLE一行转换分割为多行显示

通过oracle SQL 实现行专列，一行转多列的技术

例如：出院诊断中存在多个以@符合分割的编码，

原格式：1 张三J98.414@J84.900@G58.001@

实现的效果：

1 张三J98.414

2 张三J84.900

3 张三G58.001

二、实现的SQL语句：

select c.*,f.BZMC from(

select c.*,regexp_substr(出院诊断,'[^@]+',1,flag1) as 诊断编码from (

Select row_number() over (partition by a.出院诊断order by a.出院诊断) as flag1,a.*,b.* From (Select Rownum As Flag,

t.*

From (select a.YLBXH 医疗保险号,

a.XM 姓名,

a.BLH 住院号,

a.CYCSRQ 出院日期,

length(regexp_replace(a.CYzd, '[^@]+')) as v_count,

a.CYZD 出院诊断

from v_t_zyxxfc a) T) A,

(Select Rownum As v_Countb From Dual Connect By Rownum < 99) B

Where a.v_Count >= b.v_Countb

order by a.flag) c

--order by 2,1

) c,v_t_bzbm F

where c.诊断编码=F.bzdm

and c.出院日期Between

TO_DATE('2015-02-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') And

TO_DATE('2015-02-09 23:59:59', 'YYYY-MM-DD HH24:MI:SS')

order by c.出院日期desc