Oracle multi-hierarchical query

Question

I have several comma seperated lists (A,B,..) in different scopes (102,103,...):

SCOPE_ID  LIST_NAME  LIST_OF_NODES
102       list_A     02086,02087,02089,02131,02210,02211
102       list_B     00004,06003,06338
103       list_A     00705,02006,02017,02057,02085
103       list_B     09442,09443
...

I need to get a table of format:

SCOPE_ID  LIST_NAME NODE_ID
102       list_A    02086
102       list_A    02087
102       list_B    00004
...
103       list_A    00705
103       list_A    02006
103       list_B    09442
...

I already know how to get LIST_OF_NODES split by comma:

select SCOPE_ID, LIST_NAME, NODE_ID, 
       substr(NODE_ID,instr(NODE_ID,',',1,decode(level-1,0,1,level-1))-5,5) node 
from (
  select '102' SCOPE_ID, 
         'LIST_A' LIST_NAME, 
         '00705,02006,02017,02057,02085,02086,02087,02089,02131,02210,02211' NODE_ID 
    from dual ) 
connect by level<=regexp_count(NODE_ID,',')+2 ;

...but how to do it for multiple lists and scopes?

Answer 1

If you are able to create a query for single record
(by the way - your query is wrong, i've refined it slightly)

With my_table AS(
  select '102' SCOPE_ID, 'LIST_A' LIST_NAME, 
           '00705,02006,02017,02057,02085,02086,02087,02089,02131,02210,02211' NODE_ID 
  from dual 
)


SELECT SCOPE_ID, LIST_NAME, NODE_ID, 
       substr(NODE_ID,instr(NODE_ID,',',1,decode(level ,0,1,level ))-5,5) node 
FROM my_table
connect by level<=regexp_count(NODE_ID,',')+1 ;

then you can use LATERAL keyword to run this query against multiple records, in this way:

With my_table AS(
  select '102' SCOPE_ID, 'LIST_A' LIST_NAME, '00705,02006,02017,02057,02085,02086,02087,02089,02131,02210,02211' NODE_ID 
  from dual UNION ALL
  select '102' SCOPE_ID, 'LIST_B' LIST_NAME, '00004,06003,06338' NODE_ID 
  from dual UNION ALL 
  select '103' SCOPE_ID, 'LIST_A' LIST_NAME, '00705,02006,02017,02057,02085' NODE_ID 
  from dual
)


SELECT x.SCOPE_ID, x.LIST_NAME, x.node_id, y.NODE 
FROM my_table x,
LATERAL  (
    SELECT x.NODE_ID, 
           substr(x.NODE_ID,instr(x.NODE_ID,',',1,decode(level ,0,1,level ))-5,5) node 
    FROM dual
    connect by level<=regexp_count(x.NODE_ID,',')+1
) y