Bill of materials

from the Artful SQL Server & Access Tips List


Here is a simple Bill of Materials (BOM) tree table:
-- treedata.sql
USE test;
DROP TABLE treedata
CREATE TABLE treedata (
  ID int,
  parentID int,
  description varchar(50),
  qty INT
)
INSERT INTO treedata VALUES (1,null,'500-000',1);
INSERT INTO treedata VALUES (2,1,'500-100',1);
INSERT INTO treedata VALUES (3,1,'500-006',1);
INSERT INTO treedata VALUES (4,1,'500-004',1);
INSERT INTO treedata VALUES (5,1,'500-005',1);
INSERT INTO treedata VALUES (6,1,'500-001',1);
INSERT INTO treedata VALUES (7,2,'500-003',1);
INSERT INTO treedata VALUES (8,2,'500-002',2);
INSERT INTO treedata VALUES (9,4,'500-080',1);
INSERT INTO treedata VALUES (10,5,'500-080',1);
INSERT INTO treedata VALUES (11,null,'800-000',1);
INSERT INTO treedata VALUES (12,11,'800-004',1);
INSERT INTO treedata VALUES (13,11,'500-001',2);
INSERT INTO treedata VALUES (14,11,'800-100',1);
INSERT INTO treedata VALUES (15,11,'800-006',1);
INSERT INTO treedata VALUES (16,11,'800-005',1);
INSERT INTO treedata VALUES (17,12,'500-080',4);
INSERT INTO treedata VALUES (18,14,'800-002',1);
INSERT INTO treedata VALUES (19,14,'500-003',1);
INSERT INTO treedata VALUES (20,16,'500-080',1);
SELECT * FROM treedata
To retrieve the Bill of Materials (BOM) for all root IDs:
  • Initialise @level to 0
  • Populate #temp with all treedata.IDs that have NULL parentIDs, and string representations of those IDs
  • In a loop that repeats uintil there are no more rows
  • Increment @level
  • To #temp add each ID whose parentID=ID of previous @level, and the path to it:
-- bom.sql
DROP TABLE #temp;
SELECT *,
  0 as level,                                -- track tree level
  CAST(str(ID,10) as varchar(8000)) AS path  -- path to this node
INTO #temp                                   
FROM treedata                                
WHERE parentID is NULL                       -- root nodes
DECLARE @level INT
SET @level = 0                               -- start with level=0
WHILE @@rowcount > 0 BEGIN
  SET @level = @level + 1                    -- increment level
  INSERT INTO #temp                          -- add children of prev level
    SELECT 
      d.*,                            
      @level,                                
      t.path + str(d.ID,10)                  -- update path to this node
    FROM treedata d 
    JOIN #temp t ON d.parentID=t.ID
    AND t.level=@level-1
END
To display the tree:
SELECT SPACE(level*3)+description AS Item,qty,path
FROM #temp
ORDER by path
Item                   Path
500-000                     1
   500-100             1         2
   500-100             1         2
      500-003             1         2         7
      500-003             1         2         7
      500-003             1         2         7
      500-002             1         2         8
      500-002             1         2         8
      500-002             1         2         8
   500-006             1         3
   500-006             1         3
   500-004             1         4
   500-004             1         4
      500-080             1         4         9
      500-080             1         4         9
      500-080             1         4         9
   500-005             1         5
   500-005             1         5
      500-080             1         5        10
      500-080             1         5        10
      500-080             1         5        10
   500-001             1         6
   500-001             1         6
800-000                    11
   800-004            11        12
   800-004            11        12
      500-080            11        12        17
      500-080            11        12        17
      500-080            11        12        17
   500-001            11        13
   500-001            11        13
   800-100            11        14
   800-100            11        14
      800-002            11        14        18
      800-002            11        14        18
      800-002            11        14        18
      500-003            11        14        19
      500-003            11        14        19
      500-003            11        14        19
   800-006            11        15
   800-006            11        15
   800-005            11        16
   800-005            11        16
      500-080            11        16        20
      500-080            11        16        20
      500-080            11        16        20
To add columns to the model, add them to the treedata table and to the reporting SELECT statement.

Last updated 22 May 2009


Return to the Artful SQL Server & Access Tips page