BOM Explosion with Blanks

I have a simple BOM table that contains a parent and a child column. Every row contains values. There are no blanks. To bring that table into a BI-Tool I need to add blank values for the parents, like here shown:

As you can see, if an EntityKay has no parent key, then there should be a blank value. How would you do that?

Example:

Current State:

Child   Parent
4   1
5   1
6   2
7   3
8   3
9   3

Needed Result:

Child   Parent
1   
2   
3   
4   1
5   1
6   2
7   3
8   3
9   3

Answer

This query provides the missing keys, i.e. the parents, that are no childs are selected with the parent null

select distinct Parent Child, null Parent from bom
where Parent not in (select Child from bom)
order by 1;


     CHILD PARENT
---------- ------
         1       
         2       
         3 

You must add those records (with an INSERT) to your table and probably update additionaly the description.

insert into bom (Child, Parent)
select distinct Parent Child, null Parent from bom
where Parent not in (select Child from bom)

Result

select * from bom;

     CHILD     PARENT
---------- ----------
         4          1
         5          1
         6          2
         7          3
         8          3
         9          3
         2           
         3           
         1   

To get the extended data without modifying the table use an UNION ALL of the ofiginal table with the query providing the missing part.

You may add also the names for the new rows using the DECODE as show below in the example

select   
   Child,  Parent, name 
from BOM union all
select distinct 
  Parent Child, null Parent, 
  decode(Parent,1,'North America',2,'Europa',3,'Asia')
from bom
where Parent not in (select Child from bom)