-- #goodsbom
if object_id('tempdb.dbo.#goodsbom') is not null drop table #goodsbom
create table #goodsbom(billid int, materialid varchar(8), bomno varchar(8), quantity int)
insert into #goodsbom
select 1001, 'A', 'AA', 1 union all
select 1002, 'B', 'BB', 1 union all
select 1003, 'F', 'FF', 1 union all
select 1004, 'A', 'AK', 1

-- #goodsbomdetail
if object_id('tempdb.dbo.#goodsbomdetail') is not null drop table #goodsbomdetail
create table #goodsbomdetail(billid int, itemno int, elemgid varchar(8), quantity int, bomno varchar(8))
insert into #goodsbomdetail
select 1001, 1, 'B', 1, 'BB' union all
select 1001, 2, 'C', 1, '' union all
select 1001, 3, 'D', 1, '' union all
select 1002, 1, 'E', 1, '' union all
select 1002, 2, 'F', 2, 'FF' union all
select 1003, 1, 'G', 1, '' union all
select 1003, 2, 'H', 1, '' union all
select 1004, 1, 'J', 1, '' union all
select 1004, 2, 'K', 1, ''

-- #materialprice
if object_id('tempdb.dbo.#materialprice') is not null drop table #materialprice
create table #materialprice(materailid varchar(8), price int)
insert into #materialprice
select 'A', 2 union all
select 'B', 1 union all
select 'C', 1 union all
select 'D', 1 union all
select 'E', 1 union all
select 'F', 1 union all
select 'G', 1 union all
select 'H', 1 union all
select 'J', 1 union all
select 'K', 1

-- 1
;with cte(mid,mbom,mqty,id,qty,bom) as
(
    select a.materialid, a.bomno, a.quantity, b.elemgid, a.quantity*b.quantity, b.bomno from #goodsbom a, #goodsbomdetail b where a.billid=b.billid and a.materialid='A'
    union all
    select c.mid, c.mbom, c.mqty, b.elemgid, a.quantity*b.quantity*c.qty, b.bomno from #goodsbom a, #goodsbomdetail b, cte c where a.billid=b.billid and a.bomno=c.bom
)
select a.*, b.price, amt=a.qty*b.price from cte a, #materialprice b where a.id=b.materailid and a.bom=''
/*
mid      mbom     mqty        id       qty         bom      price       amt
-------- -------- ----------- -------- ----------- -------- ----------- -----------
A        AA       1           C        1                    1           1
A        AA       1           D        1                    1           1
A