Home Forums SQL Server 2008 SQL Server 2008 - General Recursive CTE for BOM Structures RE: Recursive CTE for BOM Structures

  • September 11, 2012 at 8:13 am

    #1536270

    SteveEClarke (9/11/2012)

    Ths is the actual resultset ( from current SP's )

    TopLevel LevelAssembly Component QtyPer Route

    CD150M-BS-020 1CD150M-BS-020 CD150-CI-ACW-16 1.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 11-0386-0115 1.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 12-0282-3065 1.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 11-0259-0125 1.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 12-0283-0115 1.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 35-0430-8412 4.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 43-0763-9912 10.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 35-0445-8132 6.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 35-0435-8412 4.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 12-0284-0115 1.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 38-1004-4112 1.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 38-0620-9913 1.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 38-0700-4112 1.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 41-0586-9911 1.0000000

    CD150M-BS-020 341-0586-9911 41-0230-9912 1.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 38-1445-9912 1.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 38-1446-9912 1.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 28-0255-8011 1.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 38-1036-4112 1.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 41-0241-6112 1.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 43-1506-8112 2.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 54-1131-9912 1.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 35-0230-3612 4.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 35-0201-6162 4.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 21-0230-0115 1.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 23-0586-9923 1.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 35-0320-8412 1.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 35-0301-8762 1.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 35-0330-8418 1.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 35-0301-8172 1.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 35-0440-8132 6.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 35-0401-8762 12.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 35-0401-8182 12.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 33-0644-8132 8.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 33-0601-8762 8.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 33-0601-8182 8.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 47-0148-7913 1.0000000

    CD150M-BS-020 347-0148-7913 47-0332-6113 1.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 35-0420-8412 2.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 38-1034-5413 1.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 38-1034-5423 1.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 38-1034-5433 1.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 38-1034-5443 2.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 36-0646-6513 1.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 36-0646-6523 1.0000000

    CD150M-BS-020 2CD150-CI-ACW-16 36-0646-6533 1.0000000

    CD150M-BS-020 1CD150M-BS-020 MA1242 1.0000000

    CD150M-BS-020 2MA1242 13-0174-0215 1.0000000

    CD150M-BS-020 2MA1242 16-0400-8041 1.0000000

    CD150M-BS-020 2MA1242 39-0069-9912 2.0000000

    CD150M-BS-020 2MA1242 13-0172-8811 1.0000000

    CD150M-BS-020 2MA1242 41-0232-9912 1.0000000

    CD150M-BS-020 2MA1242 38-0809-4112 2.0000000

    CD150M-BS-020 2MA1242 13-0173-0115 1.0000000

    CD150M-BS-020 2MA1242 35-0225-8412 3.0000000

    CD150M-BS-020 2MA1242 35-0201-8762 3.0000000

    CD150M-BS-020 2MA1242 51-0003-9912 1.0000000

    CD150M-BS-020 2MA1242 51-0010-9912 1.0000000

    CD150M-BS-020 2MA1242 43-1048-9912 1.0000000

    CD150M-BS-020 2MA1242 43-1788-9912 1.0000000

    CD150M-BS-020 2MA1242 38-1033-4112 1.0000000

    CD150M-BS-020 2MA1242 45-0281-8812/170 1.0000000

    CD150M-BS-020 345-0281-8812/170 45-0281-8812 0.2000000

    CD150M-BS-020 2MA1242 36-0650-9913 1.0000000

    CD150M-BS-020 2MA1242 36-0650-9923 1.0000000

    CD150M-BS-020 2MA1242 36-0650-9933 1.0000000

    CD150M-BS-020 2MA1242 36-0650-9943 2.0000000

    CD150M-BS-020 2MA1242 36-0650-9953 1.0000000

    CD150M-BS-020 2MA1242 43-0733-9912 1.0000000

    CD150M-BS-020 1CD150M-BS-020 V150-14 1.0000000

    CD150M-BS-020 2V150-14 23-0590-0125 1.0000000

    CD150M-BS-020 2V150-14 39-0652-1213 1.0000000

    CD150M-BS-020 2V150-14 23-0591-4113 1.0000000

    CD150M-BS-020 2V150-14 33-9560-8132 4.0000000

    CD150M-BS-020 2V150-14 33-0501-8182 4.0000000

    CD150M-BS-020 2V150-14 35-0501-8762 4.0000000

    CD150M-BS-020 2V150-14 23-0592-0125 1.0000000

    CD150M-BS-020 2V150-14 38-0961-4112 1.0000000

    CD150M-BS-020 2V150-14 33-9540-8132 4.0000000

    CD150M-BS-020 2V150-14 28-0130-9912 4.0000000

    CD150M-BS-020 2V150-14 54-0923-9912 1.0000000

    CD150M-BS-020 2V150-14 33-0648-8132 8.0000000

    CD150M-BS-020 2V150-14 33-0601-8182 8.0000000

    CD150M-BS-020 2V150-14 33-0601-8762 8.0000000

    CD150M-BS-020 1CD150M-BS-020 EP25-27 1.0000000

    CD150M-BS-020 2EP25-27 23-0568-2016 1.0000000

    CD150M-BS-020 2EP25-27 23-0571-2016 1.0000000

    CD150M-BS-020 2EP25-27 38-1013-4112 1.0000000

    CD150M-BS-020 2EP25-27 38-1014-4112 1.0000000

    CD150M-BS-020 2EP25-27 23-0570-2011 1.0000000

    CD150M-BS-020 2EP25-27 23-0567-2011 1.0000000

    CD150M-BS-020 2EP25-27 23-0412-2011 1.0000000

    CD150M-BS-020 2EP25-27 39-0641-4113 1.0000000

    CD150M-BS-020 1CD150M-BS-020 AC150-49 1.0000000

    CD150M-BS-020 2AC150-49 49-0523-9912 1.0000000

    CD150M-BS-020 2AC150-49 38-1071-5613 1.0000000

    CD150M-BS-020 2AC150-49 35-0330-8412 4.0000000

    CD150M-BS-020 2AC150-49 35-0301-8762 8.0000000

    CD150M-BS-020 2AC150-49 26-1114-9912 1.0000000

    CD150M-BS-020 2AC150-49 26-1263-9912 1.0000000

    CD150M-BS-020 2AC150-49 26-1305-9915 1.0000000

    CD150M-BS-020 2AC150-49 26-1407-9912 1.0000000

    CD150M-BS-020 2AC150-49 33-0701-8762 1.0000000

    CD150M-BS-020 2AC150-49 54-0925-9915 1.0000000

    CD150M-BS-020 354-0925-9915 54-0556-9912 1.0000000

    CD150M-BS-020 2AC150-49 27-2806-8211 1.0000000

    CD150M-BS-020 2AC150-49 43-1204-7015 1.0000000

    CD150M-BS-020 343-1204-7015 43-1204-7004 1.0000000

    CD150M-BS-020 2AC150-49 43-1483-8112 1.0000000

    CD150M-BS-020 2AC150-49 43-0718-9913 1.0000000

    CD150M-BS-020 2AC150-49 43-1632-8112 1.0000000

    CD150M-BS-020 2AC150-49 27-2805-9821 1.0000000

    CD150M-BS-020 2AC150-49 35-0399-8402/110 4.0000000

    CD150M-BS-020 2AC150-49 27-2775-8515 1.0000000

    CD150M-BS-020 2AC150-49 35-0345-8132 4.0000000

    CD150M-BS-020 2AC150-49 35-0112-8412 2.0000000

    CD150M-BS-020 2AC150-49 35-0101-8182 1.0000000

    CD150M-BS-020 2AC150-49 35-0101-8762 2.0000000

    CD150M-BS-020 2AC150-49 54-0923-9912 1.0000000

    CD150M-BS-020 2AC150-49 43-0686-9912 1.0000000

    CD150M-BS-020 2AC150-49 27-3613-6513 1.0000000

    CD150M-BS-020 2AC150-49 27-3613-6523 1.0000000

    CD150M-BS-020 2AC150-49 27-3613-6533 1.0000000

    CD150M-BS-020 2AC150-49 43-1473-8112 1.0000000

    CD150M-BS-020 2AC150-49 43-0733-9912 1.0000000

    CD150M-BS-020 1CD150M-BS-020 SUPP-20 1.0000000

    CD150M-BS-020 2SUPP-20 27-2796-8211 1.0000000

    CD150M-BS-020 2SUPP-20 35-0335-8412 2.0000000

    CD150M-BS-020 2SUPP-20 35-0301-8182 2.0000000

    CD150M-BS-020 2SUPP-20 35-0301-8762 2.0000000

    CD150M-BS-020 1CD150M-BS-020 ACC-L-26 1.0000000

    CD150M-BS-020 2ACC-L-26 47-0344-7911 1.0000000

    CD150M-BS-020 347-0344-7911 47-0332-6113 1.0000000

    CD150M-BS-020 1CD150M-BS-020 ACC-L-23 1.0000000

    CD150M-BS-020 2ACC-L-23 34-0721-9912 4.0000000

    Not much help. Need the DDL (CREATE TABLE statements) for the the source tables. Some sample data (as a series of INSERT INTO statements) for each table. Expected results based on the sample data you provide.