单行拆转多行的查询

原始数据:要求将ASSETUNITID拆成多行展示，r_id是主键

 

展示效果:将ASSETUNITID拆成多行，别名取为assid

 

 

实现的sql:

selectr_id,INSTANCEID,o_name,o_code,o_type,d_flag,imp_date,pipe_id,src_r_id,fundid,b.assid ,accountid,PRODUCTID,usedate,name,sxrqfrom   STRUCTURE_INFOa

left join (

SELECT distinct regexp_substr(ASSETUNITID, '[^,]+', 1, LEVEL) AS assid ,r_id as rid

FROM STRUCTURE_INFO

CONNECT BY LEVEL <= length(ASSETUNITID) -

length(REPLACE(ASSETUNITID, ',')) + 1)b

on a.r_id=b.rid