SQLでBOM表を作成（基礎編）

create table composition(
       parent varchar(3)
      ,child varchar(3)
)

with recursive rec_composition(level,parent,child,class,product)
 as 
 (
 select cast(1 as numeric) as level
       ,c.parent
       ,c.child
       ,cast(c.child as text) as class
       ,c.child as product
   from composition c
  where c.parent is null
 union 
 select p.level + 1 as level
       ,c.parent
       ,c.child
       ,p.class || '->' || c.child as class
       ,cast(substring(p.class from 1 for 3) as varchar(3)) as product
   from rec_composition p
  inner join composition c
     on p.child = c.parent
 )
 select level
       ,parent
       ,child
       ,class
       ,product
   from rec_composition
 order by product
      ,level
      ,parent
      ,child

with recursive rec_composition(level,parent,child,class,product)
 as 
 (
 --非再帰。最終完成品の親品番のみ抽出
 select cast(1 as numeric) as level --階層1で固定
       ,c.parent --nullが返ります
       ,c.child --自身です
       ,cast(c.child as text) as class --階層の流れです。自身です
       ,c.child as product --自身です。構成品グループとなります。
   from composition c
  where c.parent is null --ここでの構成品マスタのルールとして、最終完成品の親品番はnullです
 union 
 --再帰。階層2以下を処理
 select p.level + 1 as level --階層2以下をカウントアップ
       ,c.parent --親品番
       ,c.child --子品番
       ,p.class || '->' || c.child as class --非再帰の階層1から順に階層の流れを積み上げ
       ,cast(substring(p.class from 1 for 3) as varchar(3)) as product --構成品グループ。カッコ悪いですが、階層の流れの前3桁を取得。品番の桁数が統一されていない場合、lpad等で桁数を揃える
   from rec_composition p
  inner join composition c
     on p.child = c.parent
 )
--with句の内容を返す
 select level
       ,parent
       ,child
       ,class
       ,product
   from rec_composition
 order by product
      ,level
      ,parent
      ,child