BOM表中有主件品號,元件品號
TOP_BOM_ID = BOM_ID

可以使用CTE+inner join遞迴
優點 : 可以使用索引

declare @主件 nvarchar(20) = 'A'; --要搜尋的BOMID
with cte as (
    select [主件],[元件],1 as level,cast([主件]+[元件] as nvarchar(max)) as Ord
    from T
    where [主件] = @主件
    union all
    select T1.[主件],T1.[元件],T2.level + 1 as level,cast(T2.ord + T1.[元件] as nvarchar(max)) as ord
    from T T1
    inner join cte T2 on T1.主件 = T2.元件
)
select  
'|'+(REPLICATE('　|', level-1) + cast(level AS nvarchar(5)))+'|' 階層
,[主件],[元件]
from cte
order by ord

結果

線上測試連結

測試DDL

CREATE TABLE T
	([主件] nvarchar(1), [元件] nvarchar(1))
;
	
INSERT INTO T
	([主件], [元件])
VALUES
	('A', 'B'),
	('A', 'C'),
	('A', 'D'),
	('B', 'E'),
	('B', 'F'),
	('B', 'G')
;

新問題

如果是要一次搜尋所有主件呢，搜尋結果都在同一張表單??

使用DENSE_RANK來分組BOM並去掉where bom_id篩選

with cte as (
	select DENSE_RANK() over (order by [主件]) GroupID,*
	from #T
)
,cte2 as (
    select GroupID,[主件],[元件],1 as level,cast([主件]+[元件] as nvarchar(max)) as Ord
    from cte
    union all
    select T2.GroupID,T1.[主件],T1.[元件],T2.level + 1 as level,cast(T2.ord + T1.[元件] as nvarchar(max)) as ord
    from cte T1
    inner join cte2 T2 on T1.主件 = T2.元件
)
select  
	GroupID as 第幾組,'|'+(REPLICATE('　|', level-1) + cast(level AS nvarchar(5)))+'|' 階層
	,[主件],[元件]
from cte2
order by GroupID,ord

線上測試連結

給您參考
[MS SQL] SQL 挑戰 - 排列組合和階層目錄 (使用 CTE 遞迴)
https://ithelp.ithome.com.tw/articles/10202028

使用階層方法查詢階層式資料表
https://docs.microsoft.com/zh-tw/sql/relational-databases/tables/lesson-2-3-querying-a-hierarchical-table-using-hierarchy-methods?view=sql-server-2014

很久以前在網路上找到的

新增 function

CREATE function [dbo].[bomtree](@MD001 CHAR(20))
returns @t table(MD001 char(20),MD002 char(4),MD003 char(20),MD004 char(4),MD006 numeric(11,3),MD007 numeric(4,0),
                ITEM int,sort Nvarchar(1000)  ) 
as
begin
     declare @ITEM int
     set @ITEM=1
     insert into @t
     select MD001,MD002,MD003,MD004,MD006,MD007,@ITEM,MD001+MD003
     from BOMMD
     where MD001=@MD001
     while @@rowcount>0
     begin
         set @ITEM=@ITEM+1
         insert @t
         select a.MD001,a.MD002,a.MD003,a.MD004,a.MD006*b.MD006,a.MD007,@ITEM,b.sort+a.MD003
         from  BOMMD a ,@t b
         where  a.MD001=b.MD003 
                and b.ITEM=@ITEM-1   
    end
return
end 
GO

使用方式
select * from bomtree('主件品號') order by sort

以上!
請依貴司ERP來修正
希望能幫您一點忙!