title: Production BoM explosion drill through phantom BoM
body: <p>This is my first post, although Stack Overflow helped me numerous times so far. Hope this time will be same way.</p>
  
  <p>I have tried to build recurring query and UDF, using sources and code found online, but with no luck. Just got more and more confused.</p>
  
  <p>What I have:</p>
  
  <ul>
  <li>Items table - with Item No and BoM No</li>
  <li>BoMLines table - with Bom No and all sub Items No <strong>OR phantom BoM No's</strong></li>
  </ul>
  
  <p><br /><br />
  What I'm after:</p>
  
  <p>Basically I need to list of all <strong>items</strong> with all of their <strong>sub items</strong> and <strong>quantity per</strong>.
  Every thing seemed to be OK, until I found out we also have phantom BoMs(Bom's calling other BoMs) and those have to be 'drilled in' to item level.</p>
  
  <p><br /><br />
  <strong>Result expected:</strong>
  <a href="http://i.stack.imgur.com/sTkFp.png" rel="nofollow">Screenshot</a></p>
  
  <p><strong>Which is table with list of all items with BoM explode to next item!!</strong></p>
  
  <p><br /><br /></p>
  
  <p>To show easier what I'm after DDL included:</p>
  
  <pre><code>-- Drop table Items if exist
  IF OBJECT_ID('test.Items') IS NOT NULL
      DROP TABLE test.Items;
  
  -- Create Items table
  CREATE TABLE test.Items (
    ItemID varchar(30) COLLATE Latin1_General_CI_AS NOT NULL,
    Description varchar(100) COLLATE Latin1_General_CI_AS NOT NULL,
    Prod_BOM_No varchar(30) COLLATE Latin1_General_CI_AS NULL,
    PRIMARY KEY CLUSTERED (ItemID)
      WITH (
        PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF,
        ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
  )
  ON [PRIMARY]
  
  -- Drop table BOMLines if exist
  IF OBJECT_ID('test.BOMLines') IS NOT NULL
      DROP TABLE test.BOMLines;
  
  -- Create BOMLines table
  CREATE TABLE test.BOMLines (
    AutoID int IDENTITY(1, 1) NOT NULL,
    Prod_BOM_No varchar(30) COLLATE Latin1_General_CI_AS NOT NULL,
    Type tinyint DEFAULT 1 NOT NULL,
    No_ varchar(30) COLLATE Latin1_General_CI_AS NOT NULL,
    Qty_Per decimal(18, 8) DEFAULT 1.5 NULL,
    PRIMARY KEY CLUSTERED (AutoID)
      WITH (
        PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF,
        ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
  )
  ON [PRIMARY]
  </code></pre>
  
  <p>And also DML to show example of data I work on:</p>
  
  <pre><code>--Insert Test Items to Items table
  INSERT INTO 
    test.Items
  (ItemID, Description, Prod_BOM_No) 
  VALUES 
  ('I1001', 'Item I1001', 'BM1010'), ('I1002', 'Item I1002', 'BM1020'), ('I1003', 'Item I1003', 'BM1030'), 
  ('I1004', 'Item I1004', 'BM1040'), ('I1005', 'Item I1005', 'BM1050'), ('I1006', 'Item I1006', 'BM1060'), 
  ('I1007', 'Item I1007', 'BM1070'), ('I1008', 'Item I1008', 'BM1080'), ('I1009', 'Item I1009', 'BM1090'), 
  ('I1010', 'Item I1010', 'BM1100'), ('I5001', 'Item I5001', ''), ('I5002', 'Item I5002', ''), 
  ('I5003', 'Item I5003', ''), ('I5004', 'Item I5004', ''), ('I5005', 'Item I5005', ''), 
  ('I5006', 'Item I5006', ''), ('I5007', 'Item I5007', ''), ('I5008', 'Item I5008', ''), 
  ('I5009', 'Item I5009', ''), ('I5010', 'Item I5010', ''), ('I5011', 'Item I5011', ''), 
  ('I5012', 'Item I5012', ''), ('I5013', 'Item I5013', ''), ('I5014', 'Item I5014', ''), 
  ('I5015', 'Item I5015', ''), ('I5016', 'Item I5016', ''), ('I5017', 'Item I5017', ''), 
  ('I5018', 'Item I5018', ''), ('I5019', 'Item I5019', ''), ('I5020', 'Item I5020', '');
  
  -- Insert test BOMs to BOMLines table
  INSERT INTO 
    test.BOMLines
  ( Prod_BOM_No, No_, [Type])
  VALUES 
  ('BM1010', 'I5001', 1), ('BM1020', 'I5002', 1), ('BM1020', 'I5003', 1), 
  ('BM1030', 'BM1031', 2), ('BM1030', 'I5004', 1), ('BM1031', 'I5005', 1), 
  ('BM1040', 'I5006', 1), ('BM1050', 'I5007', 1), ('BM1050', 'BM1051', 2), 
  ('BM1051', 'BM1052', 2), ('BM1052', 'I5008', 1), ('BM1060', 'I5009', 1), 
  ('BM1060', 'I5010', 1), ('BM1060', 'I5011', 1), ('BM1060', 'BM1061', 2), 
  ('BM1061', 'I5012', 1), ('BM1061', 'I5013', 1), ('BM1061', 'BM1062', 2), 
  ('BM1062', 'I5013', 1), ('BM1062', 'I5014', 1), ('BM1070', 'I5015', 1), 
  ('BM1080', 'I5016', 1), ('BM1080', 'I5017', 1), ('BM1080', 'I5018', 1), 
  ('BM1090', 'I5019', 1), ('BM1100', 'I5020', 1);
  </code></pre>