单行拆转多行的查询

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

 

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

 

 

实现的sql:

select r_id , INSTANCEID , o_name , o_code , o_type , d_flag , imp_date , pipe_id , src_r_id , fundid , b . assid  , accountid , PRODUCTID , usedate ,name, sxrq from    STRUCTURE_INFO a

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  

 

 

 

 

 

 

 

转载于:https://www.cnblogs.com/Yggdrasil/p/7274051.html