Tree Query Problem

Starting with SQL Server 2008, you can use hierarchyid to represent tree data, that way you don't have to reinvent the wheel:

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

INSERT @BOMStructure 
(PartNumber ,Descript ,Qty ,Price ,ItemNumber)
VALUES  ('00150060060005','BASIC TANK',1,0,'/1/'),
                ('11012142200503','SHELL',1,789.89,'/1/1/'),
                ('12052140503','TOP CONE',1,226.75,'/1/2/'),
                ('13052140503','BOTTOM CONE',1,226.75,'/1/3/'),
                ('140104116508','PIPE LEG',3,39.75,'/1/4/'),
                ('15004104','BALL FEET',3,0,'/1/5/'),
                ('1510413504','SLEEVE',1,18.03,'/1/5/1/'),
                ('1524809510','ADJUSTABLE BOLT',1,12.82,'/1/5/2/'),
                ('1530411604','BASE',1,7.27,'/1/5/3/')
-- Mengupdate 
update @BOMStructure
set TotalPrice   = 0 
where PartNumber in 
(
select PartNumber  
from @BOMStructure   
);
                
-- Mengisi Table Total Price
update @BOMStructure 
set TotalPrice = Price * Qty;

SELECT * FROM @BOMStructure;

/*
00150060060005  BASIC TANK      1       0.00    0.00    0x58
11012142200503  SHELL   1       789.89  789.89  0x5AC0
12052140503     TOP CONE        1       226.75  226.75  0x5B40
13052140503     BOTTOM CONE     1       226.75  226.75  0x5BC0
140104116508    PIPE LEG        3       39.75   119.25  0x5C20
15004104        BALL FEET       3       0.00    0.00    0x5C60
1510413504      SLEEVE  1       18.03   18.03   0x5C6B
1524809510      ADJUSTABLE BOLT 1       12.82   12.82   0x5C6D
1530411604      BASE    1       7.27    7.27    0x5C6F
*/

Example for hierarchyid tree structure query:

http://ift.tt/1gjtT6S

---

Kalman Toth Database & OLAP Architect SELECT Video Tutorials 4 Hours

New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012