SQL Server 2012

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table t
	([bom_no] varchar(5), [prd_no] varchar(1), [child_bom_no] varchar(5))
;
	
insert into t
	([bom_no], [prd_no], [child_bom_no])
values
	('a->a0', 'b', null),
	('a->a0', 'c', 'c->a0'),
	('a->a0', 'd', 'd->a0'),
	('c->a0', 'e', null),
	('c->a0', 'f', 'f->a0'),
	('d->a0', 'g', null),
	('f->a0', 'h', null),
	('f->a0', 'i', 'i->a0'),
	('i->a0', 'j', null),
	('i->a0', 'k', null),
	('l->a0', 'm', null)	
;
11 rows affected

select * from T


bom_no
prd_no
child_bom_no


a->a0
b
null

a->a0
c
c->a0

a->a0
d
d->a0

c->a0
e
null

c->a0
f
f->a0

d->a0
g
null

f->a0
h
null

f->a0
i
i->a0

i->a0
j
null

i->a0
k
null

l->a0
m
null



declare @bom_no varchar(55) = 'c->a0';
with cte as (
    select * from T 
    where bom_no = @bom_no
    union all
    select T1.* from T T1
    inner join cte T2 on T1.bom_no = T2.Child_BOM_NO
    where T2.bom_no is not null
)
select @bom_no as top_bom_no,* from cte;






top_bom_no
bom_no
prd_no
child_bom_no


c->a0
c->a0
e
null

c->a0
c->a0
f
f->a0

c->a0
f->a0
h
null

c->a0
f->a0
i
i->a0

c->a0
i->a0
j
null

c->a0
i->a0
k
null


declare @bom_no varchar(55) = 'f->a0';
with cte as (
    select * from T 
    where bom_no = @bom_no
    union all
    select T1.* from T T1
    inner join cte T2 on T1.bom_no = T2.Child_BOM_NO
    where T2.bom_no is not null
)
select @bom_no as top_bom_no,* from cte;


top_bom_no
bom_no
prd_no
child_bom_no


f->a0
f->a0
h
null

f->a0
f->a0
i
i->a0

f->a0
i->a0
j
null

f->a0
i->a0
k
null


declare @bom_no varchar(55) = 'i->a0';
with cte as (
    select * from T 
    where bom_no = @bom_no
    union all
    select T1.* from T T1
    inner join cte T2 on T1.bom_no = T2.Child_BOM_NO
    where T2.bom_no is not null
)
select @bom_no as top_bom_no,* from cte;


top_bom_no
bom_no
prd_no
child_bom_no


i->a0
i->a0
j
null

i->a0
i->a0
k
null

bom_no	prd_no	child_bom_no
a->a0	b	null
a->a0	c	c->a0
a->a0	d	d->a0
c->a0	e	null
c->a0	f	f->a0
d->a0	g	null
f->a0	h	null
f->a0	i	i->a0
i->a0	j	null
i->a0	k	null
l->a0	m	null