create table deathstar_power_nodes ( 
  id integer not null, 
  label varchar2(100), 
  primary_node_fk integer, 
  constraint deathstar_power_nodes_pk primary key (id), 
  constraint deathstar_power_node_fk_prim foreign key ( primary_node_fk ) 
    references deathstar_power_nodes( id ) 
)

delete from deathstar_power_nodes

insert all  
  into deathstar_power_nodes( id, primary_node_fk, label ) values ( 1, null, 'Primary 1' )  
  into deathstar_power_nodes( id, primary_node_fk, label ) values ( 2, 1, 'Secondary 1.1' )  
  into deathstar_power_nodes( id, primary_node_fk, label ) values ( 3, 1, 'Secondary 1.2' )  
  into deathstar_power_nodes( id, primary_node_fk, label ) values ( 4, null, 'Primary 2' )  
  into deathstar_power_nodes( id, primary_node_fk, label ) values ( 5, 4, 'Secondary 2.1' )  
  into deathstar_power_nodes( id, primary_node_fk, label ) values ( 6, null, 'Primary 3' )  
select * from dual

create or replace view v_deathstar_grouped_power_nodes as 
  -- We first need to arrange the related nodes into groups 
  with node_groups as ( 
    select group_entry.id group_id, -- Group-ID is the ID of the primary node 
           members.id member_id 
      from deathstar_power_nodes group_entry 
      -- We join the base-table with itself to get all 
      -- related entries for each row 
      inner join deathstar_power_nodes members 
            -- using either the primary-node reference, 
            -- or if it's NULL (because it *is* the 
            -- primary node) the ID 
            on group_entry.id = nvl(members.primary_node_fk, members.id) 
      -- We only do this for primary nodes 
      where group_entry.primary_node_fk is null 
  ) 
  select nodes.id power_node_id, 
         groups.group_id, 
         groups.member_id, 
         member.label member_label, 
         case 
           when member.primary_node_fk is null then 1 
           else 0 
         end is_primary 
    from deathstar_power_nodes nodes 
    -- We join the groups via primary-node reference (if it exists) or the ID (of the primary node) 
    inner join node_groups groups 
           on nvl(nodes.primary_node_fk, nodes.id) = groups.group_id 
    -- To get more information than only the ID of the group-members we need to join our base table again 
    inner join deathstar_power_nodes member 
           on groups.member_id = member.id

select * from v_deathstar_grouped_power_nodes where power_node_id = 1

select * from v_deathstar_grouped_power_nodes where power_node_id = 2

select * from v_deathstar_grouped_power_nodes where power_node_id = 3

select * from v_deathstar_grouped_power_nodes where power_node_id = 4

select * from v_deathstar_grouped_power_nodes where power_node_id = 5

select * from v_deathstar_grouped_power_nodes where power_node_id = 6