Here's my sample code for the bill of material explosion problem. Using the BOL article on "Expanding Hierarchies"... I took the example code and modified it as below. My problem is that it needs the top level BOM in order to start the expansion, whereas I want the output from the expansion of all the top level BOM's in my list of BOM's....
================================================================
Product structure is parent part and child part.
Held in bom table
I need to expand this table to show parent part, and each child part, (including treating each child part as
a parent part and expanding that hierarchy too)
bom table:
parent part child part
1 2
1 3
1 4
1 5
1 6
1 7
2 a
2 b
2 c
3 12
3 13
3 14
4 x
4 y
4 z
9 c
9 d
9 e
desired output, bom_expanded table:
1
2
3
4
5
6
7
a
b
c
12
13
14
x
y
z
9
c
d
e
actual output using the sample code below is missing the last 4 parts:
bom_expanded table:
1
2
3
4
5
6
7
a
b
c
12
13
14
x
y
z
----------------------
--===== Drop test tables if they exist
IF OBJECT_ID('TempDB..#dmBOM','U') IS NOT NULL
DROP TABLE #dmBOM
IF OBJECT_ID('TempDB..#dmBOM_expanded','U') IS NOT NULL
DROP TABLE #dmBOM_expanded
IF OBJECT_ID('TempDB..#dmSTACK','U') IS NOT NULL
DROP TABLE #dmSTACK
CREATE TABLE #dmBOM (parent_item varchar(20), child_item varchar(20))
INSERT INTO #dmBOM VALUES ('1', '2')
INSERT INTO #dmBOM VALUES ('1', '3')
INSERT INTO #dmBOM VALUES ('1', '4')
INSERT INTO #dmBOM VALUES ('1', '5')
INSERT INTO #dmBOM VALUES ('1', '6')
INSERT INTO #dmBOM VALUES ('1', '7')
INSERT INTO #dmBOM VALUES ('2', 'a')
INSERT INTO #dmBOM VALUES ('2', 'b')
INSERT INTO #dmBOM VALUES ('2', 'c')
INSERT INTO #dmBOM VALUES ('3', '12')
INSERT INTO #dmBOM VALUES ('3', '13')
INSERT INTO #dmBOM VALUES ('3', '14')
INSERT INTO #dmBOM VALUES ('4', 'x')
INSERT INTO #dmBOM VALUES ('4', 'y')
INSERT INTO #dmBOM VALUES ('4', 'z')
INSERT INTO #dmBOM VALUES ('9', 'c')
INSERT INTO #dmBOM VALUES ('9', 'd')
INSERT INTO #dmBOM VALUES ('9', 'e')
CREATE TABLE #dmBOM_expanded (item varchar(20))
CREATE TABLE #dmSTACK (item varchar(20), level int)
DECLARE @current varchar(20)
DECLARE @level int
DECLARE @line varchar(20)
SET @current = '1'
INSERT INTO #dmSTACK VALUES (@current, 1)
SET @level = 1
WHILE @level > 0
BEGIN
IF EXISTS (SELECT * FROM #dmSTACK WHERE level = @level)
BEGIN
SELECT @current = item
FROM #dmSTACK
WHERE level = @level
SELECT @line = @current
INSERT INTO #dmBOM_expanded select @line
DELETE FROM #dmSTACK WHERE level = @level AND item = @current
INSERT #dmSTACK
SELECT [child_item], @level + 1
FROM #dmBOM
WHERE [parent_item] = @current
IF @@ROWCOUNT > 0
SELECT @level = @level + 1
END -- BEGIN
ELSE
SELECT @level = @level - 1
END -- WHILE
select * from #dmBOM_expanded
order by item
------------------------------
-- clean up
IF OBJECT_ID('TempDB..#dmBOM','U') IS NOT NULL
DROP TABLE #dmBOM
IF OBJECT_ID('TempDB..#dmBOM_expanded','U') IS NOT NULL
DROP TABLE #dmBOM_expanded
IF OBJECT_ID('TempDB..#stack','U') IS NOT NULL
DROP TABLE #stack
--------------------------------