with t_n as(

select rownum as N from dual

connect by rownum<=200

)

,cte as (

      select 'equity_asia1_dif' tb,'ID_BB_GLOBAL|RC' pk from dual union all

      select 'fundamentals_asia1_af_out','ID_BB_GLOBAL|BC_DT|BC_EQY_DVD_ADJUST_FACT|BC_EQY_DVD_ADJ_FUND_OPER|BC_EQY_DVD_ADJ_FUND_FLAG' from dual union all

      select 'fundamentals_asia1_bs_out','ID_BB_GLOBAL|FISCAL_YEAR_PERIOD|FILING_STATUS|EQY_CONSOLIDATED|ACCOUNTING_STANDARD' from dual

),res as (

  select a.*,b.*,nvl(lag(b.n) over(partition by a.tb order by b.n),0) as head

  from cte a inner join t_n b on length(a.pk||'|')>=b.n

  where substr(a.pk||'|',b.n,1)='|'

)

select tb,substr(pk,head+1,n-head-1),pk

from res

order by tb,n;

with a as (

select 'equity_asia1_dif' tb,'ID_BB_GLOBAL|RC' pk from dual union all

      select 'fundamentals_asia1_af_out','ID_BB_GLOBAL|BC_DT|BC_EQY_DVD_ADJUST_FACT|BC_EQY_DVD_ADJ_FUND_OPER|BC_EQY_DVD_ADJ_FUND_FLAG' from dual union all

      select 'fundamentals_asia1_bs_out','ID_BB_GLOBAL|FISCAL_YEAR_PERIOD|FILING_STATUS|EQY_CONSOLIDATED|ACCOUNTING_STANDARD' from dual

)

select  t1.tb,regexp_substr(pk,'[^|]+',1,t2.lv) pk from a t1,

(select level lv from (select max(regexp_count(pk,'[^|]+',1))+1 ct from a) b connect by level<b.ct) t2

where regexp_substr(pk,'[^|]+',1,t2.lv) is not null

order by 1,t2.lv