Oracle将多行某列转成一行某列，一行某列转成多行某列

select wm_concat(line_id) from dcr_decision_opinion group by bill_id

    select *
             from (with temp as (select wm_concat(line_id) a,bill_id
                                 from dcr_decision_opinion group by bill_id
                                  )
                    select regexp_substr(a, '[^,]+', 1,rn) aa,bill_id
                      from temp t1 ,
                           (select distinct level rn
                              from temp ta
                            connect by rownum <=
                                       (select length(a) -
                                               length(replace(a,
                                                              ',',
                                                              '')) + 1
                                          from temp tb
                                         where ta.bill_id = tb.bill_id)) t2  )where aa is not null order by bill_id

 

更简单的形式：

 select * from ( select regexp_substr(member_names, '[^;]+', 1, t2.rn) DcrNumberName,
                           regexp_substr(regexp_substr(member_names,  '[^;]+',  1,   t2.rn),  '\d+') userid,
                           role_name,
                           Bill_Id,
                           line_id,
                           CREATED_BY,
                           CREATED_BY_NAME,
                           LAST_UPDATED_BY,
                           LAST_UPDATED_BY_NAME
                      from tableName t1,
                          (select rownum rn from tableName where rownum<=50)t2
                     where enabled_flag = 'Y' and role_code != 'DecisionGroupRole_10'   ) where   DcrNumberName is not null order by line_id