ms sql 2008 recursion?

I have a list of items,  but some "items" are kits of other items. I am trying to get a list of all items.

Example Items a,b,c,d (kit with a,b), e, f (kit c,d),
There is another table that lists if item is a "final item"  - the opposite being a kit.

Selecting all items, I would like to get the list:
a (for kit d)
b (for kit d)
c (for kit f)
a (for kit f,d)
b (for kit f,d)
Who is Participating?
rajeevnandanmishraConnect With a Mentor Commented:
Considering the structure of your tables, try the below code:

with childList (item, child, remarks) as (
select a.item_num, a.pkg_num, convert(varchar(100),a.pkg_num) as remarks  
from pkg_list a, tableA b where a.pkg_num = b.item and = 'no' 
union all 
select a.item_num, b.item, convert(varchar(100),b.remarks + '->' + b.item) as remarks 
from childList b, pkg_list a, tableA c  where b.item = a.pkg_num and b.item = c.item and = 'no'
select a.pkg_num, '' for_kit 
from pkg_list a, tableA b where a.pkg_num = b.item and = 'yes' 
union all 
select a.item, a.remarks 
from childList a, tableA b where a.item = b.item and = 'yes' 
order by 2

Open in new window

Kindly provide the structure of your two tables. That will be easy to provide the solution.
Because when we say the data is like
a,b,c,d (kit with a,b),
Then what the column values are, is it like:
Key1 Key2
A       A
B       B
C       C
D       A
D       A
or whatever....
freshgrillAuthor Commented:

item, final
a  yes
b yes
c yes
d no
e yes
f no

pkg_num item_num
a       a
b       b
c      a
c      a
d      d
e      a
f      f
g    c
g    e

Basically, 1 table that list a pkg/item number and the pkg contents (i.e. items in the pkg). and a second table that list all items and if they are a "final" item or a kit.
Scott PletcherSenior DBACommented:
(Classic bill-of-materials essentially)
List the required result.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.