declare @BOMStructure as table  
(
PartNumber varchar(14)not null ,
Descript varchar(50)not null,
Qty integer not null default 0,
Price Decimal (10,2) default 0,
ItemNumber hierarchyid not null primary key 
)

INSERT @BOMStructure 
(PartNumber ,Descript ,Qty ,Price ,ItemNumber)
VALUES	('00150060060005','BASIC TANK',1,0,'/'),
		('11012142200503','SHELL',3,100,'/1/'),
		('12052140503','TOP CONE',1,0,'/2/'),
		('13052140503','BOTTOM CONE',2,100,'/2/1/'),
		('140104116508','PIPE LEG',3,50,'/2/2/'),
		('1510413504','SLEEVE',2,0,'/3/'),
		('1524809510','ADJUSTABLE BOLT',1,100,'/3/1/'),
		('1530411604','BASE',1,100,'/3/2/')
		
		
-- GetAncestor
-- Mengupdate 
select  PartNumber, Descript,Qty,Price,
			(select sum (Price * qty)
               from @BOMStructure
               where ItemNumber .IsDescendantOf (p.ItemNumber ) = 1
              ) as [TotalPrice], 
        ItemNumber .ToString() as [Hierarcy], ItemNumber .GetLevel() as [Level]
from @BOMStructure as P;

WITH Work (PartNumber, Descript, Qty, Price, SubTotalPrice, ItemNumber) AS (
	SELECT PartNumber, Descript, Qty, Price, Price * Qty, ItemNumber
	FROM @BOMStructure Base
	WHERE NOT EXISTS (
		SELECT * FROM @BOMStructure Filter
		WHERE Filter.ItemNumber.GetAncestor(1) = Base.ItemNumber)
	UNION ALL
	SELECT Base.PartNumber, Base.Descript, Base.Qty, Base.Price, Convert(Decimal(10, 2), Work.SubTotalPrice) * Base.Qty, Base.ItemNumber
	FROM @BOMStructure Base
	INNER JOIN Work
		ON Work.ItemNumber.GetAncestor(1) = Base.ItemNumber
)
SELECT PartNumber, Descript, Qty, Price, SUM(SubTotalPrice) TotalPrice, ItemNumber.ToString() [Hierarcy], ItemNumber.GetLevel() [Level] FROM Work
GROUP BY PartNumber, Descript, Qty, Price, ItemNumber
ORDER BY ItemNumber