SQL Recursive CTE 'where-used' / BOM explosion

Question

What I'm trying to do simply is for an item from the BOM table (Bill of Materials) get it's related components/products from BOMVERSION and then for those related components/products get their related components and products etc. down to 7 levels. I then want to pivot the results so that the related items get put in columns 0, 1, 2, 3, 4, 5, 6, 7

Please see attached example data and code. I'm using dynamics AX 2012 R2 but this example could be applied to any system that uses materials/products. I cannot get my query to complete however (I know I don't have dataareaid and partition I've left out for simplicity). the relationship is an itemid from BOM table is related to itemid in BOMVERSION through BOMID.

UPDATE : I've simplified data etc. to make as simple/clear as possible, so materials wood, metal, glass can go into making various products and materials themselves, and can be combined to make products or materials. Therefore I want to start with a base component, then explode the relationships out by levels.

DDL + DML:

    USE tempdb;

IF OBJECT_ID('tempdb..#BOM') IS NOT NULL
    DROP TABLE #BOM;

CREATE TABLE #BOM
    (
      ITEMID NVARCHAR(10) ,
      BOMID NVARCHAR(10) ,
      MATERIALNAME NVARCHAR(10)
    );

INSERT  INTO #BOM
VALUES  ( N'113621', -- ITEMID - nvarchar(10)
          N'1',  -- BOMID - nvarchar(10)
          N'Wood'  -- MATERIALNAME - nvarchar(10)
          );

INSERT  INTO #BOM
VALUES  ( N'234517', -- ITEMID - nvarchar(10)
          N'2',  -- BOMID - nvarchar(10)
          N'Metal'  -- MATERIALNAME - nvarchar(10)
          );

INSERT  INTO #BOM
VALUES  ( N'378654', -- ITEMID - nvarchar(10)
          N'3',  -- BOMID - nvarchar(10)
          N'Glass'  -- MATERIALNAME - nvarchar(10)
          );

IF OBJECT_ID('tempdb..#BOMVERSION') IS NOT NULL
    DROP TABLE #BOMVERSION;

CREATE TABLE #BOMVERSION
    (
      ITEMID NVARCHAR(10) ,
      BOMID NVARCHAR(10) ,
      NAME NVARCHAR(20)
    );

INSERT  INTO #BOMVERSION
VALUES  ( N'113477', -- ITEMID - nvarchar(10)
          N'1', -- BOMID - nvarchar(10)
          N'Oak'  -- NAME - nvarchar(10)
          );

INSERT  INTO #BOMVERSION
VALUES  ( N'113608', -- ITEMID - nvarchar(10)
          N'1', -- BOMID - nvarchar(10)
          N'Pine'  -- NAME - nvarchar(10)
          );

INSERT  INTO #BOMVERSION
VALUES  ( N'113622', -- ITEMID - nvarchar(10)
          N'1', -- BOMID - nvarchar(10)
          N'Wood Table'  -- NAME - nvarchar(10)
          );

INSERT  INTO #BOMVERSION
VALUES  ( N'113683', -- ITEMID - nvarchar(10)
          N'2', -- BOMID - nvarchar(10)
          N'Aluminium'  -- NAME - nvarchar(10)
          );

INSERT  INTO #BOMVERSION
VALUES  ( N'113689', -- ITEMID - nvarchar(10)
          N'2', -- BOMID - nvarchar(10)
          N'Steel'  -- NAME - nvarchar(10)
          );

INSERT  INTO #BOMVERSION
VALUES  ( N'113693', -- ITEMID - nvarchar(10)
          N'2', -- BOMID - nvarchar(10)
          N'Metal table'  -- NAME - nvarchar(10)
          );

INSERT  INTO #BOMVERSION
VALUES  ( N'113694', -- ITEMID - nvarchar(10)
          N'3', -- BOMID - nvarchar(10)
          N'Glass'  -- NAME - nvarchar(10)
          );

INSERT  INTO #BOMVERSION
VALUES  ( N'113695', -- ITEMID - nvarchar(10)
          N'3', -- BOMID - nvarchar(10)
          N'Glass BookCase'  -- NAME - nvarchar(10)
          );

--Query

WITH    BOM1
          AS ( SELECT   B.ITEMID AS BITEMID ,
                        BV.ITEMID AS BVITEMID ,
                        B.MATERIALNAME ,
                        B.BOMID
               FROM     #BOM AS B
                        JOIN #BOMVERSION AS BV ON BV.BOMID = B.BOMID
             ),
        EXPLODE
          AS ( SELECT   B.BITEMID ,
                        B.MATERIALNAME ,
                        B.BVITEMID ,
                        B.BOMID ,
                        0 AS [Level]
               FROM     BOM1 AS B
               UNION ALL
               SELECT   B.BITEMID ,
                        E.MATERIALNAME ,
                        E.BVITEMID ,
                        E.BOMID ,
                        [E].[Level] + 1
               FROM     EXPLODE AS E
                        JOIN BOM1 AS B ON B.BOMID = E.BOMID
               WHERE    E.Level <= 6   --narrowing levels                   
             )
    SELECT  *
    FROM    EXPLODE PIVOT ( MAX(BVITEMID) FOR Level IN ( [0], [1], [2], [3],
                                                         [4], [5], [6], [7] ) ) AS PVTBOM;

Output to look like enter image description here

Well, you get +1 for including ddl + dml for sample data, but -1 since it's unclear what you are asking. — Zohar Peled, May 14 '15 at 09:38
Hi, I don't know how i can make it any clearer... get an item, through bomid get related item, get related item to that item, etc. etc. pivot by levels — jhowe, May 14 '15 at 09:40

Giorgi Nakeuri · Accepted Answer · 2015-05-14T13:59:11.830

Well this is just to eliminate errors and get results, but I don't know if the results are correct:

WITH    BOM1
          AS ( SELECT   B.ITEMID AS BITEMID ,
                        BV.ITEMID AS BVITEMID ,
                        B.BOMID
               FROM     #BOM AS B
                        JOIN #BOMVERSION AS BV ON BV.BOMID = B.BOMID
             ),
        EXPLODE
          AS ( SELECT   B.BITEMID ,
                        B.BVITEMID ,
                        B.BOMID ,
                        0 AS [Level]
               FROM     BOM1 AS B
               UNION ALL
               SELECT   B.BITEMID ,
                        E.BVITEMID ,
                        E.BOMID ,
                        [E].[Level] + 1
               FROM     EXPLODE AS E
                        JOIN BOM1 AS B ON B.BOMID = E.BOMID
               WHERE e.Level <= 6   --narrowing levels                   
             )


    SELECT  *
    FROM    EXPLODE PIVOT ( MAX(BVITEMID) FOR Level IN ( [0], [1], [2], [3],
                                                         [4], [5], [6], [7] ) ) AS PVTBOM

You had maxrecursion = 7 - this is not the place to narrow the levels. Levels are narrowed inside recursive part in CTE.

I suspect your recursion is not well organized, so can you simplify your test data? Just leave several rows and show the expected output.

EDIT1:

WITH    p AS ( SELECT   *
               FROM     ( SELECT    itemid ,
                                    bomid ,
                                    ROW_NUMBER() OVER ( PARTITION BY Bomid ORDER BY itemid ) rn
                          FROM      #BOMVERSION
                        ) t PIVOT ( MAX(itemid) FOR rn IN ( [1], [2], [3], [4],
                                                            [5], [6], [7], [8] ) ) p
             )
    SELECT  *
    FROM    #bom b
JOIN p ON b.bomid = p.bomid

EDIT2:

;WITH    cte
          AS ( SELECT   b.itemid AS originalitem ,
                        b.bomid AS originalbom ,
                        b.bomid ,
                        bv.itemid AS parent ,
                        0 AS level
               FROM     #BOM b
                        JOIN #BOMVERSION bv ON bv.bomid = b.bomid
               UNION ALL
               SELECT   c.originalitem ,
                        c.originalbom ,
                        b.bomid ,
                        bv.itemid ,
                        c.level + 1
               FROM     cte c
                        JOIN #BOM b ON c.parent = b.itemid
                        JOIN #BOMVERSION bv ON bv.bomid = b.bomid
               WHERE    c.level <= 6
             ),
        tree
          AS ( SELECT   originalitem ,
                        originalbom ,
                        parent ,
                        level
               FROM     cte
             )
    SELECT  *
    FROM    tree PIVOT ( MAX(parent) FOR level IN ( [0], [1], [2], [3], [4], [5], [6], [7] ) ) AS p

Thanks adding the level limit was a great help, I can at least get the query to run properly now. Let me see what this looks like with proper data... will feedback shortly. — jhowe, May 14 '15 at 09:49
Hi i've updated and tried to make as simple as possible. I'm just trying to show the relationships between the items, so Wood makes a wood table and goes into pine and oak etc. glass goes into making glass bookcase and wood goes into it as well, that sort of thing... — jhowe, May 14 '15 at 11:32
Hi but won't this only show items related to the first item? I want to show items related to the first item but then items related to THAT item, do I not need a recursive CTE for that? so basically the query is looping round the items/levels ... — jhowe, May 14 '15 at 12:37
really appreciate you helping me on this... yea that's not going to give me what I want... so let me try and explain again. sorry trying to make it simple has hidden the complexity. Item 113621 has related items i.e. 113477. But then item 113477 could have it's OWN components that make up that item.... do you see what i mean? So it needs to work like this. enter item from BOM table, get BOMIDs, find related items in bomversion table, go back to BOM table to see if THOSE items have their own BOMID's etc. that are then related to even more items in BOMVERSION... do that 7 times. i.e. recurs. — jhowe, May 14 '15 at 13:23
I came up with an almost identical solution myself, however I was missing the 'tree' bit. Can I ask why this made a difference? — jhowe, May 14 '15 at 14:49
and one more thing in the 'level' sections if I wanted to add more detail i.e. the description of the item into each 'level' section is that possible? — jhowe, May 14 '15 at 15:59
@jhowe, yes it is possible. Just add additional column you want exactly like originalbom - originalmaterialname etc. The difference that makes a tree is that the pivot has 3 elements, aggregation i.e. parent, spreading i.e. level, and(this is important) grouping element is determined by elimination of columns. That meann that pivot will group by result set by all columns except spreading column and aggregation column. Read about pivot for more details. — Giorgi Nakeuri, May 14 '15 at 17:22

SQL Recursive CTE 'where-used' / BOM explosion

1 Answers1

Linked