sql - How to get a root node and all its child nodes for multiple hierarchy in a table -

i have following heirarchies in db

a |_a1 |  |_a11 | |_a2 

and

b |_b1 | |_b2    |_b21    |_b22 

i want query displays result as

parent    child          a1          a11          a2 b          b1 b          b2 b          b21 b          b22 

with w(child, parent) (   select 'a', null dual   union   select 'a1', 'a' dual   union   select 'a11', 'a1' dual   union   select 'a2', 'a' dual   union   select 'b', null dual   union   select 'b1', 'b' dual   union   select 'b2', 'b' dual   union   select 'b21', 'b2' dual   union   select 'b22', 'b2' dual ) select connect_by_root child parent, child w connect w.parent = prior w.child start w.parent null ; 

this returns:

1     2     a1 3     a11 4     a2 5   b   b 6   b   b1 7   b   b2 8   b   b21 9   b   b22 

you can filter lines parent == child adding where connect_by_root child != child between from , connect.