sql - Convert transitive closure table into adjacency list -

i have materialized transitive closures table called graph_tbl. code below

i running oracle 11gr2.

i want apologize providing incomplete data question. please see correct , more complete data below:

my table

create table "graph_tbl" ("parent_name" varchar2(80 char),  "child_name" varchar2(80 char),  "parent_id" varchar2(18 char),  "child_id" varchar2(18 char),  "relative_level" number(18,0) ); 

data:

insert graph_tbl parent_name,child_name,parent_id,child_id,relative_level) values ('components','components','a044100000171bxaaq','a044100000171bxaaq',0); insert graph_tbl (parent_name,child_name,parent_id,child_id,relative_level) values ('processors','processors','a044100000171byaaq','a044100000171byaaq',0); insert graph_tbl (parent_name,child_name,parent_id,child_id,relative_level) values ('intel','intel','a044100000171bzaaq','a044100000171bzaaq',0); insert graph_tbl (parent_name,child_name,parent_id,child_id,relative_level) values ('xeon 5600','xeon 5600','a044100000171bdaaa','a044100000171bdaaa',0); insert gpmetlstage.graph_tbl (parent_name,child_name,parent_id,child_id,relative_level) values ('intel','xeon 5600','a044100000171bzaaq','a044100000171bdaaa',1); insert graph_tbl (parent_name,child_name,parent_id,child_id,relative_level) values ('processors','intel','a044100000171byaaq','a044100000171bzaaq',1); insert graph_tbl (parent_name,child_name,parent_id,child_id,relative_level) values ('processors','xeon 5600','a044100000171byaaq','a044100000171bdaaa',2); insert graph_tbl (parent_name,child_name,parent_id,child_id,relative_level) values ('xeon 5600','intel xeon e5645 2.4ghz, 12m cache,turbo, ht, 1333mhz max mem','a044100000171bdaaa','a044100000171graaa',1); insert graph_tbl (parent_name,child_name,parent_id,child_id,relative_level) values ('components','processors','a044100000171bxaaq','a044100000171byaaq',1); insert graph_tbl (parent_name,child_name,parent_id,child_id,relative_level) values ('eclipse products , services','eclipse products , services','a044100000171aqaaq','a044100000171aqaaq',0); insert graph_tbl (parent_name,child_name,parent_id,child_id,relative_level) values ('components','intel','a044100000171bxaaq','a044100000171bzaaq',2); insert graph_tbl (parent_name,child_name,parent_id,child_id,relative_level) values ('components','xeon 5600','a044100000171bxaaq','a044100000171bdaaa',3); insert graph_tbl (parent_name,child_name,parent_id,child_id,relative_level) values ('eclipse products , services','processors','a044100000171aqaaq','a044100000171byaaq',2); insert graph_tbl (parent_name,child_name,parent_id,child_id,relative_level) values ('eclipse products , services','intel','a044100000171aqaaq','a044100000171bzaaq',3); insert graph_tbl (parent_name,child_name,parent_id,child_id,relative_level) values ('eclipse products , services','xeon 5600','a044100000171aqaaq','a044100000171bdaaa',4); insert graph_tbl (parent_name,child_name,parent_id,child_id,relative_level) values ('eclipse products , services','components','a044100000171aqaaq','a044100000171bxaaq',1); insert graph_tbl (parent_name,child_name,parent_id,child_id,relative_level) values ('intel xeon e5645 2.4ghz, 12m cache,turbo, ht, 1333mhz max mem','intel xeon e5645 2.4ghz, 12m cache,turbo, ht, 1333mhz max mem','a044100000171graaa','a044100000171graaa',0); commit; 

this sample of 1 path leaf level root of tree. particular tree looks top leaf node:

eclipse products , services (this root, category)  components (some category)   processors (some category)    intel (some category)     xeon 5600 (some category)      intel xeon e5645 2.4ghz, 12m cache,turbo, ht, 1333mhz max mem (this leaf node, product) 

relative level indicates edges of graph categories:
0 - node itself
1 - next direct immediate node (direct parent - child relationship)
2 - 1 hop over
3 - 2 hops over
4 - 3 hops on

relative levels >=2 defined categories not leaf (actual product)

please me convert adjacency list using sql. expected output should this:

name                                                            id  parent_id eclipse products , services                                   a044100000171aqaaq  null components                                                      a044100000171bxaaq  a044100000171aqaaq processors                                                      a044100000171byaaq  a044100000171bxaaq intel                                                           a044100000171bzaaq  a044100000171byaaq xeon 5600                                                       a044100000171bdaaa  a044100000171bzaaq intel xeon e5645 2.4ghz, 12m cache,turbo, ht, 1333mhz max mem   a044100000171graaa  a044100000171bdaaa 

thank time , help!

try this.

with tbl(parent_id,child_id) (select 1,2 dual union select 1,3 dual union select 1,4 dual union select 1,5 dual union select 2,3 dual union select 2,4 dual union select 2,5 dual union select 4,5 dual ) select      nvl(c.child_id,p.parent_id) id     ,c.parent_id      (select child_id,     max(parent_id) parent_id tbl      group child_id     ) c full join     (select distinct parent_id parent_id tbl) p on c.child_id=p.parent_id order id 

output

+----+-----------+ | id | parent_id | +----+-----------+ |  1 |           | |  2 |         1 | |  3 |         2 | |  4 |         2 | |  5 |         4 | +----+-----------+ 

explanation:

the first derived table c max(parent) group child. in case, child 2,3,4,5. 1 has no child, not given in output. it, need full join on distinct parent_id parent there no child.

we need nvl or coalesce or case in select clause take parent_id derived table p when child_id is null.