SQL Query

the table design is incomplete, as you will not be able to tell which level 2 is parent of which level 3, for example.

see, you have 2 level2: R001 and R006
how to tell which one is parent of R004, for example?

unless you have some other field in the table that can help to identify this "relationship", you have to fully review the table design and fix it.

Given your sample input, how do you define the parent child relationship? For example,
there are multiple level 2 partnos:

Product  Level.     PartNo
F10000.  2            R001
F10000.  2            R006

How do you relate R005 to R001 instead of R006 as in the given desired output?

Product  Level.     PartNo    Parent        Child
F10000.  3            R005          R001           R005

Please clarify,

I think there should be sequential order logic.
As part no #R005 is on Level 3, therefore it's parent should be Level 2 upper level part no of #R001 and child is itself #R005.

>sequential order logic.
is there a field that shows the sequence?
without such a field, you cannot "rely" on the records being returned in the "order" they have been entered.

if you refer to the value of xxx in Rxxx, this will become very tricky, and again does not comply to proper database schema.

please clarify

Is this what you need?

declare @testBOM as table (Product varchar(10), [Level] int, PartNo varchar(10))
insert into @testBOM
select 'F10000',1,'W001' union all
select 'F10000',2,'R001' union all
select 'F10000',3,'R005' union all
select 'F10000',3,'R004' union all
select 'F10000',2,'R006' union all
select 'F10000',3,'R007' union all
select 'F10000',4,'R002' union all
select 'F10000',3,'R008' union all
select 'F10000',3,'R009'

select
		b.*,
		isnull(bParent.PartNo, b.Product) as Parent,
		b.PartNo as Child
from 
		@testBOM b
		left outer join @testBOM bParent on (bParent.[Level] + 1 = b.[Level])

Select allOpen in new window

Product Level PartNo      Parent      Child
F10000      1      W001      F10000      W001
F10000      2      R001      W001      R001
F10000      3      R005      R001      R005
F10000      3      R005      R006      R005
F10000      3      R004      R001      R004
F10000      3      R004      R006      R004
F10000      2      R006      W001      R006
F10000      3      R007      R001      R007
F10000      3      R007      R006      R007
F10000      4      R002      R005      R002
F10000      4      R002      R004      R002
F10000      4      R002      R007      R002
F10000      4      R002      R008      R002
F10000      4      R002      R009      R002
F10000      3      R008      R001      R008
F10000      3      R008      R006      R008
F10000      3      R009      R001      R009
F10000      3      R009      R006      R009

Have you considered using one self join table replacing the two tables you have?

something similar to:

ID      PartID      ParentID
1      F10000             <--  this is master part thus has no parent
2      R001      10
3      R002      5
4      R004      2
5      R005      2
6      R006      4
7      R007      2
8      R008      2
9      R009      2
10   W001      1

In this case, you will have 0 to many levels for different products. Some will have 3 levels the other could have 7 levels or as necessary.

In your example, you have 4 parents for R002 (R004, R005, R007, AND R008). How this can be?

You can have only one parent  like R002 --> R005  and then R005 --> R001.

R001 becomes grand parent of Roo2 (like R002 --> --> R001).

Mike

This way using some queries and functions you would be able to produce:

ID      PartID      ParentID_1      Parent_1        Parent_2    ....   <-- you can specify the number of  
1      F10000       (master part has no ParentID)                            levels you want to pass it as pram.
2      R001          10                         W001            F10000     ...          For n parts, the number of level
3      R002            5                         R005              R001     ....             columns could potentially go  
4      R004           2                          R001              W001   ....             to n-1 levels (columns).
5      R005           2                          R001              W001   ....
6      R006          4                          R004              R001   ....
7      R007          2                          R001              W001   ....
8      R008          2                          R001              W001   ....
9      R009         2                          R001              W001   ....
10   W001        1                          F10000

Please try this. I am using a rank for each row within a Product. I don't use an Order By so that rank is determined in the order they appear. Pickup the previous rank as the parent for each Part. I am using a condition 'where Product = 'F10000.' - you can try changing that criteria for other Products.

With CTE as 
(select Product, Level, PartNo 
, ROW_NUMBER() Over (Partition by Product) as PartNumberRank 
from YourProductTable a
where Product = 'F10000.')
Select Product, Level, PartNo
      ,(Select PartNo from CTE c2 where c2.Product = c1.Product and c2.PartNumberRank = c1.PartNumberRank - 1) as Parent 
	  , PartNo as Child
  from CTE c1

Select allOpen in new window