Solved

# ms sql 2008 recursion?

Posted on 2012-03-29
311 Views
I have a list of items,  but some "items" are kits of other items. I am trying to get a list of all  physical.final 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
b
c
a (for kit d)
b (for kit d)
e
c (for kit f)
a (for kit f,d)
b (for kit f,d)
0
Question by:freshgrill

LVL 9

Expert Comment

Hi,
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....
0

Author Comment

tableA

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

pkg_list
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.
0

LVL 69

Expert Comment

(Classic bill-of-materials essentially)
0

LVL 30

Expert Comment

List the required result.
0

LVL 9

Accepted Solution

rajeevnandanmishra earned 500 total points
Hi,
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 b.final = '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 c.final = 'no'
)
select a.pkg_num, '' for_kit
from pkg_list a, tableA b where a.pkg_num = b.item and b.final = 'yes'
union all
select a.item, a.remarks
from childList a, tableA b where a.item = b.item and b.final = 'yes'
order by 2
``````
0

## Featured Post

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.