Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table mytable as (

select

1 ID,

'T1;T2;T3' column_1,

'B1;B5;B10;B13' column_2

from dual

union all

select

2 ID,

'T7;T8;T9;T10;T11',

'B2;B3;B5'

from dual

);

2 rows affected

WITH DATAA AS (

SELECT DISTINCT

ID,

REGEXP_SUBSTR(COLUMN_1, '[^;]+', 1, LEVEL) COLUMN_1,

REGEXP_SUBSTR(COLUMN_2, '[^;]+', 1, LEVEL) COLUMN_2

FROM

MYTABLE

CONNECT BY REGEXP_SUBSTR(COLUMN_1, '[^;]+', 1, LEVEL) IS NOT NULL

OR REGEXP_SUBSTR(COLUMN_2, '[^;]+', 1, LEVEL) IS NOT NULL

)

SELECT

ID,

COLUMN_1,

COLUMN_2

FROM

(

SELECT DISTINCT

D1.ID,

D1.COLUMN_1,

D2.COLUMN_2

FROM

DATAA D1

JOIN DATAA D2 ON ( D1.ID = D2.ID )

)

WHERE

( COLUMN_1 IS NOT NULL

AND COLUMN_2 IS NOT NULL )

ORDER BY

ID,

COLUMN_1;