Задачка

[dmitry_privalov]

Task:

create table MANGESH_TEST_TAB
(
MTT_FAC_ID NUMBER not null,
MTT_FACILITY VARCHAR2(100) not null
)

In this table,

INSERT INTO MANGESH_TEST_TAB Values ( 1600 , 'FAC001||FAC002||FAC003||FAC004');

INSERT INTO MANGESH_TEST_TAB Values ( 1604 , 'FAC010||FAC011||FAC012');

COMMIT;

My expected o/p :

MTT_FAC_ID MTT_FACILITY
=============================================================
1600 FAC001
1601 FAC002
1602 FAC003
1603 FAC004
1604 FAC010
1605 FAC011
1606 FAC012
------------------

Solution:

select
mtt_fac_id + level - 1, level,
regexp_substr(mtt_facility, '[^||]+', 1, level) from  mangesh_test_tab
where regexp_substr(mtt_facility, '[^||]+', 1, level) is not null
connect by regexp_instr (mtt_facility, '||', 1, level - 1) > 0
and prior dbms_random.value is not null
and prior rowid = rowid