Q55877712, Oracle - rextester

Language: Layout:

with table1(var_name,var_desc) as
    (
     select 'Test 1',
            'Code identifying test 1
             Acceptable values:
             00=No code
             01=Additional Code' from dual union all
     select 'Test 2',
            'Code identifying test 2
             Acceptable value:
             44=No code'         from dual union all
     select 'Test 3',
            'Code identifying test 3' from dual           
    ), table2(var_name,str1,str2) as
   (
   select var_name,
          case when regexp_count(var_desc,chr(10)) > 0 then
               regexp_substr(var_desc,'[^=]+',regexp_instr(var_desc,chr(10), 1, level))
          end,
          case when regexp_count(var_desc,chr(10)) > 0 then
               regexp_substr(var_desc,'=(.*)+',regexp_instr(var_desc,chr(10), 1, level))
          end
     from table1
    connect by level <= regexp_count(var_desc,chr(10)) 
        and prior var_name = var_name
        and prior sys_guid() is not null   
   )    
   select t1.*, 
          decode(regexp_count(str1,chr(10)),1,str1) as code, ltrim(str2,'=') as description
     from table2 t2
     join table1 t1 on t1.var_name = t2.var_name
    where decode(regexp_count(str1,chr(10)),1,str1) is not null
    order by t1.var_name, code


absolute service time: 1,18 sec