exploded bill of materials

I have a table with bill of material information.  I would like to be able to generate a report that shows for each Parent_Part_Num, all of the sub components and any of those sub components that have sub components.

For example:

Parent_Part_Num

123456-001
.............Lvl1....222222-001
....................Lvl2....333333-001
....................Lvl2....444444-001
.............Lvl1....555555-001
.............Lvl1....666666-001


My bill of material source table (BOM_RAW) is structured like this.

Parent_Part_Num      Child_Part_Num
123456-001                555555-001
123456-001                666666-001
222222-001                333333-001
222222-001                444444-001

Is this type of report possible?
FairfieldAsked:
Who is Participating?
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
CREATE TABLE toDelete (Parent_Part_Num  VARCHAR(100), Child_Part_Num VARCHAR(100) )
INSERT INTO dbo.toDelete
        ( Parent_Part_Num, Child_Part_Num )
VALUES  ('123456-001','222222-001'), ('123456-001','555555-001'), ('222222-001','333333-001'), ('222222-001','444444-001')
INSERT INTO dbo.toDelete VALUES  ('444444-001','888888-001')
;WITH MyCTE
AS ( SELECT Parent_Part_Num, Child_Part_Num, levl = 1
FROM toDelete t
WHERE NOT EXISTS (SELECT 1 FROM toDelete t1 WHERE t.Parent_Part_Num = t1.Child_Part_Num)
UNION ALL
SELECT MyCTE.Parent_Part_Num, e.Child_Part_Num, MyCTE.levl+1
FROM toDelete e
INNER JOIN MyCTE ON e.Parent_Part_Num = MyCTE.Child_Part_Num
--WHERE Employee.ManagerID IS NOT NULL
 )
SELECT *
FROM MyCTE
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
are you sure the data you provided is correct ?
0
 
FairfieldAuthor Commented:
Yes, it is for example only.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
FairfieldAuthor Commented:
The report could even be like this.

PARENT_PART_NUM         LEVEL         CHILD
123456-001                       01               222222-001
123456-001                       01               333333-001
123456-001                       02               444444-001
123456-001                       02               555555-001
123456-001                       03               666666-001
0
 
FairfieldAuthor Commented:
Thank you for responding.  Do I have specifically call out the parent and child numbers?  Is it just possible to have the output look like the following?

Parent_Part Number     Level       Child_Part_Num
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
change the last statement to this

SELECT Parent_Part Number, levl as [Level] ,Child_Part_Num
 FROM MyCTE
0
 
FairfieldAuthor Commented:
All this seems to give me is everything that is level 1, I was hoping to have multiple levels for Parent_Part_Num
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
can you export your data into an excel file and post it here; with the sample data, I am getting levels up to 3
0
 
FairfieldAuthor Commented:
This works great.
0
 
FairfieldAuthor Commented:
I am getting an error

The statement terminated. The maximum recursion 100 has been exhausted before statement completion
0
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
SELECT Parent_Part Number, levl as [Level] ,Child_Part_Num
 FROM MyCTE
option (maxrecursion 0)
0
 
FairfieldAuthor Commented:
You are right, I selected the wrong portion of the solution.  How do I reopen?
0
 
FairfieldAuthor Commented:
I don't know if I need to create a new question but how would I use a where statement to only find BOM's from a input table to join Parent_Part_Number?
0
All Courses

From novice to tech pro — start learning today.