SQL Recursive CTE 'where-used' / BOM explosion part 2

Question

Hi this carries on from another post,

SQL Recursive CTE 'where-used' / BOM explosion

which the original requirement was answered however I have realised I now have another final requirement. The data that I have after '1' i.e. description etc., want to repeat for each level so the description correctly identifies with the correct parent item. I tried just adding the columns in again in the final select however it just repeated items from level 1. How can this be done?

* UPDATE * I'm really struggling to get the test data/query to look how i want. I want to get the items to relate to each other through bomid. Output to look like img.

The relationship between BOM and BOMVERSION is one item has many BOMID's in BOM table each bomID has a corresponding record in BOMVERSION which through BOMID you get a different ITEMID off of BOMVERSION. That itemID can exist itself in the BOM table with MULTIPLE BOMIDs. I know this is confusing and is very difficult to demonstrate with test data. That's why i'm happy to put on bounty.

* UPDATE * through what i've learnt i've redone the query/test data. I haven't been able to get it to do EXACTLY what i want so query/data may need to be tweaked or added. I'll output what i'm expecting. Basically when BOM.ItemID is linked to BV.ItemID through BOMID i would expect BV.ItemID to be moved into next level and then if THAT BOM.ItemID links to another BV.ItemID move that item to the next level etc. etc. along with all other info RELATED to item in level.

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


CREATE TABLE #BOM
    (
      ItemID NVARCHAR(10) ,
      BOMID NVARCHAR(10) ,
      BOMQTY INT ,
      UnitID NVARCHAR(10) ,
      BOMQTYSERIE INT
    );

INSERT  INTO #BOM
        ( ItemID ,
          BOMID ,
          BOMQTY ,
          UnitID ,
          BOMQTYSERIE
        )
VALUES  ( N'100001' , -- ItemID - nvarchar(10)
          N'1A' , -- BOMID - nvarchar(10)
          10 , -- BOMQTY - int
          N'g' , -- UnitID - nvarchar(10)
          5  -- Bomqtyserie - int
        );

INSERT  INTO #BOM
        ( ItemID ,
          BOMID ,
          BOMQTY ,
          UnitID ,
          BOMQTYSERIE
        )
VALUES  ( N'100001' , -- ItemID - nvarchar(10)
          N'2A' , -- BOMID - nvarchar(10)
          15 , -- BOMQTY - int
          N'kg' , -- UnitID - nvarchar(10)
          13  -- Bomqtyserie - int
        );

INSERT  INTO #BOM
        ( ItemID ,
          BOMID ,
          BOMQTY ,
          UnitID ,
          BOMQTYSERIE
        )
VALUES  ( N'100001' , -- ItemID - nvarchar(10)
          N'3A' , -- BOMID - nvarchar(10)
          16 , -- BOMQTY - int
          N'l' , -- UnitID - nvarchar(10)
          16  -- Bomqtyserie - int
        );

INSERT  INTO #BOM
        ( ItemID ,
          BOMID ,
          BOMQTY ,
          UnitID ,
          BOMQTYSERIE
        )
VALUES  ( N'100002' , -- ItemID - nvarchar(10)
          N'1A' , -- BOMID - nvarchar(10)
          18 , -- BOMQTY - int
          N'g' , -- UnitID - nvarchar(10)
          17  -- Bomqtyserie - int
        );

INSERT  INTO #BOM
        ( ItemID ,
          BOMID ,
          BOMQTY ,
          UnitID ,
          BOMQTYSERIE
        )
VALUES  ( N'100004' , -- ItemID - nvarchar(10)
          N'2A' , -- BOMID - nvarchar(10)
          20 , -- BOMQTY - int
          N'kg' , -- UnitID - nvarchar(10)
          11  -- Bomqtyserie - int
        );

INSERT  INTO #BOM
        ( ItemID ,
          BOMID ,
          BOMQTY ,
          UnitID ,
          BOMQTYSERIE
        )
VALUES  ( N'100002' , -- ItemID - nvarchar(10)
          N'2A' , -- BOMID - nvarchar(10)
          23 , -- BOMQTY - int
          N'kg' , -- UnitID - nvarchar(10)
          19  -- Bomqtyserie - int
        );

INSERT  INTO #BOM
        ( ItemID ,
          BOMID ,
          BOMQTY ,
          UnitID ,
          BOMQTYSERIE
        )
VALUES  ( N'100003' , -- ItemID - nvarchar(10)
          N'2A' , -- BOMID - nvarchar(10)
          25 , -- BOMQTY - int
          N'kg' , -- UnitID - nvarchar(10)
          21  -- Bomqtyserie - int
        );

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


CREATE TABLE #BOMVERSION
    (
      ItemID NVARCHAR(10) ,
      BOMID NVARCHAR(10) ,
      Name NVARCHAR(20) ,
      Active BIT
    );

INSERT  INTO #BOMVERSION
        ( ItemID ,
          BOMID ,
          Name ,
          Active
        )
VALUES  ( N'100002' , -- ItemID - nvarchar(10)
          N'1A' , -- BOMID - nvarchar(10)
          N'100002 Version' , -- Name - nvarchar(10)
          1  -- Active - bit
        );

INSERT  INTO #BOMVERSION
        ( ItemID ,
          BOMID ,
          Name ,
          Active
        )
VALUES  ( N'100002' , -- ItemID - nvarchar(10)
          N'2A' , -- BOMID - nvarchar(10)
          N'100002.1 Version' , -- Name - nvarchar(10)
          1  -- Active - bit
        );

INSERT  INTO #BOMVERSION
        ( ItemID ,
          BOMID ,
          Name ,
          Active
        )
VALUES  ( N'100003' , -- ItemID - nvarchar(10)
          N'3A' , -- BOMID - nvarchar(10)
          N'100003 Version' , -- Name - nvarchar(10)
          1  -- Active - bit
        );

INSERT  INTO #BOMVERSION
        ( ItemID ,
          BOMID ,
          Name ,
          Active
        )
VALUES  ( N'100004' , -- ItemID - nvarchar(10)
          N'4A' , -- BOMID - nvarchar(10)
          N'100004 Version' , -- Name - nvarchar(10)
          1  -- Active - bit
        );

INSERT  INTO #BOMVERSION
        ( ItemID ,
          BOMID ,
          Name ,
          Active
        )
VALUES  ( N'100005' , -- ItemID - nvarchar(10)
          N'5A' , -- BOMID - nvarchar(10)
          N'100005 Version' , -- Name - nvarchar(10)
          1  -- Active - bit
        );

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


CREATE TABLE #INVENTTABLE
    (
      ItemID NVARCHAR(10) ,
      Name NVARCHAR(20) ,
      Product INT
    );

INSERT  INTO #INVENTTABLE
        ( ItemID, Name, Product )
VALUES  ( N'100001', -- ItemID - nvarchar(10)
          N'100001 Name', -- Name - nvarchar(10)
          1  -- Product - int
          );

INSERT  INTO #INVENTTABLE
        ( ItemID, Name, Product )
VALUES  ( N'100002', -- ItemID - nvarchar(10)
          N'100002 Name', -- Name - nvarchar(10)
          2  -- Product - int
          );

INSERT  INTO #INVENTTABLE
        ( ItemID, Name, Product )
VALUES  ( N'100003', -- ItemID - nvarchar(10)
          N'100003 Name', -- Name - nvarchar(10)
          3  -- Product - int
          );

INSERT  INTO #INVENTTABLE
        ( ItemID, Name, Product )
VALUES  ( N'100004', -- ItemID - nvarchar(10)
          N'100004 Name', -- Name - nvarchar(10)
          4  -- Product - int
          );

INSERT  INTO #INVENTTABLE
        ( ItemID, Name, Product )
VALUES  ( N'100005', -- ItemID - nvarchar(10)
          N'100005 Name', -- Name - nvarchar(10)
          5  -- Product - int
          );

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

CREATE TABLE #ECORESPRODUCTTRANSLATION
    (
      Product INT ,
      Name NVARCHAR(20)
    );




INSERT  INTO #ECORESPRODUCTTRANSLATION
        ( Product, Name )
VALUES  ( 1, -- Product - int
          N'100001 Description'  -- Name - nvarchar(10)
          );

INSERT  INTO #ECORESPRODUCTTRANSLATION
        ( Product, Name )
VALUES  ( 2, -- Product - int
          N'100002 Description'  -- Name - nvarchar(10)
          );

INSERT  INTO #ECORESPRODUCTTRANSLATION
        ( Product, Name )
VALUES  ( 3, -- Product - int
          N'100003 Description'  -- Name - nvarchar(10)
          );

INSERT  INTO #ECORESPRODUCTTRANSLATION
        ( Product, Name )
VALUES  ( 4, -- Product - int
          N'100004 Description'  -- Name - nvarchar(10)
          );

INSERT  INTO #ECORESPRODUCTTRANSLATION
        ( Product, Name )
VALUES  ( 5, -- Product - int
          N'100005 Description'  -- Name - nvarchar(10)
          );

WITH    CTE
          AS ( SELECT   B.ItemID AS MainItem ,
                        BV.Name AS BVName ,
                        B.BOMID ,
                        BV.ItemID AS ParentItem ,
                        ECPT.Name AS ParentItemName ,
                        B.BOMQTY ,
                        B.UnitID ,
                        B.BOMQTYSERIE ,
                        1 AS [Level]
               FROM     #BOM AS B
                        JOIN #BOMVERSION AS BV ON BV.BOMID = B.BOMID
                        JOIN #INVENTTABLE AS IT ON IT.ItemID = BV.ItemID
                        JOIN #ECORESPRODUCTTRANSLATION AS ECPT ON ECPT.Product = IT.Product
               WHERE    B.ItemID = '100001'
                        AND BV.Active = 1
               UNION ALL
               SELECT   C.MainItem ,
                        C.BVName ,
                        C.BOMID ,
                        BV.ItemID ,
                        C.ParentItemName ,
                        C.BOMQTY ,
                        C.UnitID ,
                        C.BOMQTYSERIE ,
                        C.[Level] + 1
               FROM     CTE AS C
                        JOIN #BOM AS B ON C.ParentItem = B.ItemID
                        JOIN #BOMVERSION AS BV ON BV.BOMID = B.BOMID
               WHERE    C.[Level] <= 7
             )
    SELECT  MainItem ,
            [1] AS Level1 ,
            BVName ,
            ParentItemName ,
            BOMQTY ,
            UnitID ,
            BOMQTYSERIE ,
            [2] AS Level2 ,
            BVName ,
            ParentItemName ,
            BOMQTY ,
            UnitID ,
            BOMQTYSERIE ,
            [3] AS Level3 ,
            BVName ,
            ParentItemName ,
            BOMQTY ,
            UnitID ,
            BOMQTYSERIE ,
            [4] AS Level4 ,
            BVName ,
            ParentItemName ,
            BOMQTY ,
            UnitID ,
            BOMQTYSERIE ,
            [5] AS Level5 ,
            BVName ,
            ParentItemName ,
            BOMQTY ,
            UnitID ,
            BOMQTYSERIE ,
            [6] AS Level6 ,
            BVName ,
            ParentItemName ,
            BOMQTY ,
            UnitID ,
            BOMQTYSERIE ,
            [7] AS Level7 ,
            BVName ,
            ParentItemName ,
            BOMQTY ,
            UnitID ,
            BOMQTYSERIE
    FROM    CTE PIVOT ( MAX(ParentItem) FOR [Level] IN ( [1], [2], [3], [4],
                                                         [5], [6], [7] ) ) AS pvt;

enter image description here

Hi, can you provide some sample valid data that is pivotable to your required final output and also provide final output for that data? — Giorgi Nakeuri, May 17 '15 at 15:43
@jhowe, they are not way to help you. You should simplify your data and schema. Take a look to this simple sample data http://sqlfiddle.com/#!6/3f624/5/0 and rewrite and clarify your question using it. — dani herrera, May 17 '15 at 20:09
Hi thanks, let me come up with a simple query/test data and i'll edit original post. — jhowe, May 18 '15 at 10:22
@jhowe, you should really put a little effort to provide some test input and output that is matched with that input. This way you will get more answers. — Giorgi Nakeuri, May 19 '15 at 11:30
hi @giorgi i was really struggling to create the test data and query to display what i wanted. I needed someone to suggest something to move me in the right direction. — jhowe, May 19 '15 at 13:47

score 1 · Answer 1 · edited May 23 '17 at 11:51

I think You need this type of pivoting:

select p.MainItem, p.BVName
                , left(p.[0],charindex('|',p.[0])-1) Level0_ID, REPLACE(p.[0], left(p.[0],charindex('|',p.[0])),'') Level0_Decription
                , left(p.[1],charindex('|',p.[1])-1) Level1_ID, REPLACE(p.[1], left(p.[1],charindex('|',p.[1])),'') Level1_Decription
                , left(p.[2],charindex('|',p.[2])-1) Level2_ID, REPLACE(p.[2], left(p.[2],charindex('|',p.[2])),'') Level2_Decription
                , left(p.[3],charindex('|',p.[3])-1) Level3_ID, REPLACE(p.[3], left(p.[3],charindex('|',p.[3])),'') Level3_Decription
                , left(p.[4],charindex('|',p.[4])-1) Level4_ID, REPLACE(p.[4], left(p.[4],charindex('|',p.[4])),'') Level4_Decription
                , left(p.[5],charindex('|',p.[5])-1) Level5_ID, REPLACE(p.[5], left(p.[5],charindex('|',p.[5])),'') Level5_Decription
                , left(p.[6],charindex('|',p.[6])-1) Level6_ID, REPLACE(p.[6], left(p.[6],charindex('|',p.[6])),'') Level6_Decription
                , left(p.[7],charindex('|',p.[7])-1) Level7_ID, REPLACE(p.[7], left(p.[7],charindex('|',p.[7])),'') Level7_Decription
             from (select t.MainItem, t.BVName, t.level, convert(varchar(20),t.ParentItem)+'|'+t.ParentItemName item from tree t) t
             pivot (max(item) for t.level in ([0], [1], [2], [3], [4], [5], [6], [7])) p

If You want use more column to pivot the try this:

select p.MainItem, p.BVName
    , p.Level0_ParentItem, p.Level0_ParentItemName
    , p.Level1_ParentItem, p.Level1_ParentItemName
    , p.Level2_ParentItem, p.Level2_ParentItemName
    , p.Level3_ParentItem, p.Level3_ParentItemName
    , p.Level4_ParentItem, p.Level4_ParentItemName
    , p.Level5_ParentItem, p.Level5_ParentItemName
    , p.Level6_ParentItem, p.Level6_ParentItemName
    , p.Level7_ParentItem, p.Level7_ParentItemName
from (
    select u.MainItem, u.BVName, 'Level'+convert(varchar(30), u.level)+'_'+u.col col, u.value
    from (select t.MainItem, t.BVName, t.level, convert(nvarchar(max),t.ParentItem) ParentItem, convert(nvarchar(max),t.ParentItemName) ParentItemName from tree t) t /*Convert all columns to the desired, but the same type*/
    unpivot (value for col in (ParentItem, ParentItemName /*and the other columns You want use*/)) u) src
pivot (max(src.value) 
    for src.col in (
        Level0_ParentItem, Level0_ParentItemName
        , Level1_ParentItem, Level1_ParentItemName
        , Level2_ParentItem, Level2_ParentItemName
        , Level3_ParentItem, Level3_ParentItemName
        , Level4_ParentItem, Level4_ParentItemName
        , Level5_ParentItem, Level5_ParentItemName
        , Level6_ParentItem, Level6_ParentItemName
        , Level7_ParentItem, Level7_ParentItemName
        )) p

In the unpivot statement I make the new columns for the pivot statement. More from this solution: SQL Server Pivot Table with multiple column aggregates

*** UPDATED WITH FINAL REQUIRED FORMAT ***

WITH    CTE
          AS ( SELECT   B.ITEMID AS MainItem ,
                        BV.NAME AS BVName ,
                        B.BOMID ,
                        BV.ITEMID AS ParentItem ,
                        ECPT.NAME AS ParentItemName ,
                        B.BOMQTY ,
                        B.UNITID ,
                        B.BOMQTYSERIE ,
                        1 AS [Level]
               FROM     BOM AS B
                        JOIN BOMVERSION AS BV ON BV.BOMID = B.BOMID
                        JOIN dbo.INVENTTABLE AS IT ON IT.ITEMID = BV.ITEMID
                        JOIN dbo.ECORESPRODUCTTRANSLATION AS ECPT ON ECPT.PRODUCT = IT.PRODUCT
               WHERE    B.ITEMID = '113621' /*is this really needed*/
                        AND BV.ACTIVE = 1
               UNION ALL
               SELECT   C.MainItem ,
                        C.BVName ,
                        C.BOMID ,
                        BV.ITEMID ,
                        C.ParentItemName ,
                        C.BOMQTY ,
                        C.UNITID ,
                        C.BOMQTYSERIE ,
                        C.[Level] + 1
               FROM     CTE AS C
                        JOIN BOM AS B ON C.ParentItem = B.ITEMID
                        JOIN BOMVERSION AS BV ON BV.BOMID = B.BOMID
               WHERE    C.[Level] <= 7
             ),
        TREE
          AS ( SELECT   CTE.MainItem ,
                        CTE.BVName ,
                        CTE.ParentItem ,
                        CTE.ParentItemName ,
                        CTE.BOMQTY ,
                        CTE.UNITID ,
                        CTE.BOMQTYSERIE ,
                        CTE.[Level]
               FROM     CTE
             )
    SELECT  p.MainItem ,
            p.Level1_ParentItem ,
            p.Level1_BVName ,
            p.Level1_ParentItemName ,
            p.Level1_BOMQTY ,
            p.Level1_UnitID ,
            p.Level2_ParentItem ,
            p.Level2_BVName ,
            p.Level2_ParentItemName ,
            p.Level2_BOMQTY ,
            p.Level2_UnitID ,
            p.Level3_ParentItem ,
            p.Level3_BVName ,
            p.Level3_ParentItemName ,
            p.Level3_BOMQTY ,
            p.Level3_UnitID ,
            p.Level4_ParentItem ,
            p.Level4_BVName ,
            p.Level4_ParentItemName ,
            p.Level4_BOMQTY ,
            p.Level4_UnitID ,
            p.Level5_ParentItem ,
            p.Level5_BVName ,
            p.Level5_ParentItemName ,
            p.Level5_BOMQTY ,
            p.Level5_UnitID ,
            p.Level6_ParentItem ,
            p.Level6_BVName ,
            p.Level6_ParentItemName ,
            p.Level6_BOMQTY ,
            p.Level6_UnitID ,
            p.Level7_ParentItem ,
            p.Level7_BVName ,
            p.Level7_ParentItemName ,
            p.Level7_BOMQTY ,
            p.Level7_UnitID
    FROM    ( SELECT    u.MainItem ,
                        'Level' + CAST(u.Level AS VARCHAR(20)) + '_' + u.col col ,
                        u.value
              FROM      ( SELECT    t.MainItem ,
                                    CAST(t.ParentItem AS VARCHAR(30)) AS ParentItem ,
                                    CAST(t.BVName AS VARCHAR(30)) AS BVName ,
                                    CAST(t.ParentItemName AS VARCHAR(30)) AS ParentItemName ,
                                    CAST(t.BOMQTY AS VARCHAR(30)) AS BOMQTY ,
                                    CAST(t.UNITID AS VARCHAR(30)) AS UnitID ,
                                    CAST(t.BOMQTYSERIE AS VARCHAR(30)) AS BOMQTYSERIE ,
                                    t.Level
                          FROM      TREE t
                        ) t /*Convert all columns to the desired, but the same type*/
                        UNPIVOT ( value FOR col IN ( ParentItem,
                                                     ParentItemName, BVName,
                                                     BOMQTY, UnitID,
                                                     BOMQTYSERIE                                                    
                                                      /*and the other columns You want use*/ ) ) u
            ) src PIVOT ( MAX(src.value) FOR src.col IN ( Level1_ParentItem,
                                                          Level1_BVName,
                                                          Level1_ParentItemName,
                                                          Level1_BOMQTY,
                                                          Level1_UnitID,
                                                          Level2_ParentItem,
                                                          Level2_BVName,
                                                          Level2_ParentItemName,
                                                          Level2_BOMQTY,
                                                          Level2_UnitID,
                                                          Level3_ParentItem,
                                                          Level3_BVName,
                                                          Level3_ParentItemName,
                                                          Level3_BOMQTY,
                                                          Level3_UnitID,
                                                          Level4_ParentItem,
                                                          Level4_BVName,
                                                          Level4_ParentItemName,
                                                          Level4_BOMQTY,
                                                          Level4_UnitID,
                                                          Level5_ParentItem,
                                                          Level5_BVName,
                                                          Level5_ParentItemName,
                                                          Level5_BOMQTY,
                                                          Level5_UnitID,
                                                          Level6_ParentItem,
                                                          Level6_BVName,
                                                          Level6_ParentItemName,
                                                          Level6_BOMQTY,
                                                          Level6_UnitID,
                                                          Level7_ParentItem,
                                                          Level7_BVName,
                                                          Level7_ParentItemName,
                                                          Level7_BOMQTY,
                                                          Level7_UnitID ) ) p;

this looks very hacky but it seems to be working! Is this to get around only being able to pivot 1 column? Just wondering why I have a level 1 item, then a related level 2 item but it's on the next line instead of next to the level 1 item? — jhowe, May 19 '15 at 09:59
If i wanted to add more columns in between the levels how would I do this i.e. more description columns, quantities etc. — jhowe, May 19 '15 at 10:07
Thanks what's this error? The type of column "ParentItemName" conflicts with the type of other columns specified in the UNPIVOT list. — jhowe, May 19 '15 at 10:56
In You example table BOMVERSION was the ItemID declared as nvarchar(10) and it makes no error. But Your real structure is different. I correct my answer to help on this situation. — Gabor Rajczi, May 19 '15 at 11:20
Hi i've updated with my finished code however it only displays one row... (I just want main item in the first column before the levels) any way to get all the data to display? — jhowe, May 19 '15 at 13:34
no they need to specify item or there is too much data. If I remove 'BVName' after main item which you have in your example it groups and hides all the data... ParentItemName would be ok after main item but it also needs to be used in the levels section, however i'm struggling to get it to work with parentitemname for some reason... — jhowe, May 19 '15 at 14:04
But the BVName is not for the ParrentItem, but for the MainItem. — Gabor Rajczi, May 19 '15 at 14:12
Hi Gabor thanks very much for your help but this was too complicated for me to get to work... — jhowe, May 21 '15 at 14:46

score 1 · Accepted Answer · edited May 21 '15 at 14:48

Here is a version that is easy to understand and maintain:

    ;
WITH    CTE
          AS ( SELECT   B.RECID AS MainID ,
                        B.ITEMID AS MainItem ,
                        BV.NAME ,
                        BV.ITEMID AS ParentItem ,
                        ECPT.NAME AS ParentItemName ,
                        B.BOMQTY ,
                        B.UNITID ,
                        B.BOMQTYSERIE ,
                        0 AS [level]
               FROM     #BOM B
                        JOIN #BOMVERSION BV ON BV.BOMID = B.BOMID
                        JOIN #INVENTTABLE AS IT ON IT.ITEMID = BV.ITEMID
                        JOIN #ECORESPRODUCTTRANSLATION AS ECPT ON ECPT.PRODUCT = IT.PRODUCT
               WHERE    B.ITEMID = '113621'
                        AND BV.ACTIVE = '1'
               UNION ALL
               SELECT   C.MainID ,
                        C.MainItem ,
                        C.NAME ,
                        BV.ITEMID ,
                        C.ParentItemName ,
                        C.BOMQTY ,
                        C.UNITID ,
                        C.BOMQTYSERIE ,
                        C.[level] + 1
               FROM     CTE C
                        JOIN #BOM B ON C.ParentItem = B.ITEMID
                        JOIN #BOMVERSION BV ON BV.BOMID = B.BOMID
               WHERE    C.[level] <= 6
             )
    SELECT  B.ITEMID ,
            C1.ParentItem AS ParentItem1 ,
            C1.NAME AS BVName1 ,
            C1.ParentItemName AS ParentItemName1 ,
            C1.BOMQTY AS BomQty1 ,
            C1.UNITID AS UnitID1 ,
            C1.BOMQTYSERIE AS BomQtySerie1 ,
            C2.ParentItem AS ParentItem2 ,
            C2.NAME AS BVName2 ,
            C2.ParentItemName AS ParentItemName2 ,
            C2.BOMQTY AS BomQty2 ,
            C2.UNITID AS UnitID2 ,
            C2.BOMQTYSERIE AS BomQtySerie2 ,
            C3.ParentItem AS ParentItem3 ,
            C3.NAME AS BVName3 ,
            C3.ParentItemName AS ParentItemName3 ,
            C3.BOMQTY AS BomQty3 ,
            C3.UNITID AS UnitID3 ,
            C3.BOMQTYSERIE AS BomQtySerie3 ,
            C4.ParentItem AS ParentItem2 ,
            C4.NAME AS BVName2 ,
            C4.ParentItemName AS ParentItemName4 ,
            C4.BOMQTY AS BomQty4 ,
            C4.UNITID AS UnitID4 ,
            C4.BOMQTYSERIE AS BomQtySerie4 ,
            C5.ParentItem AS ParentItem5 ,
            C5.NAME AS BVName5 ,
            C5.ParentItemName AS ParentItemName5 ,
            C5.BOMQTY AS BomQty5 ,
            C5.UNITID AS UnitID5 ,
            C5.BOMQTYSERIE AS BomQtySerie5 ,
            C6.ParentItem AS ParentItem6 ,
            C6.NAME AS BVName6 ,
            C6.ParentItemName AS ParentItemName6 ,
            C6.BOMQTY AS BomQty6 ,
            C6.UNITID AS UnitID6 ,
            C6.BOMQTYSERIE AS BomQtySerie6 ,
            C7.ParentItem AS ParentItem7 ,
            C7.NAME AS BVName7 ,
            C7.ParentItemName AS ParentItemName7 ,
            C7.BOMQTY AS BomQty7 ,
            C7.UNITID AS UnitID7 ,
            C7.BOMQTYSERIE AS BomQtySerie7
    FROM    #BOM B
            LEFT JOIN CTE C1 ON B.RECID = C1.MainID
                                AND C1.[level] = 0
            LEFT JOIN CTE C2 ON B.RECID = C2.MainID
                                AND C2.[level] = 1
            LEFT JOIN CTE C3 ON B.RECID = C3.MainID
                                AND C3.[level] = 2
            LEFT JOIN CTE C4 ON B.RECID = C4.MainID
                                AND C4.[level] = 3
            LEFT JOIN CTE C5 ON B.RECID = C5.MainID
                                AND C5.[level] = 4
            LEFT JOIN CTE C6 ON B.RECID = C6.MainID
                                AND C6.[level] = 5
            LEFT JOIN CTE C7 ON B.RECID = C7.MainID
                                AND C7.[level] = 6
    WHERE   B.ITEMID = '113621'
            AND C1.ParentItem IS NOT NULL;

EDIT:

Input:

INSERT  INTO #BOM VALUES  
( 1, N'10', N'1A' ),
( 2, N'20', N'2A' ),
( 3, N'30', N'3A' ),
( 4, N'40', N'4A' ),
( 5, N'50', N'5A' ),
( 6, N'60', N'6A' ),
( 7, N'70', N'7A' ),
( 8, N'80', N'8A' ),

( 9, N'90', N'9A' ),
( 10, N'100', N'10A' ),
( 11, N'110', N'11A' )

INSERT  INTO #BOMVERSION VALUES  
( 1, N'20', N'10 PRE', N'1A' ),
( 2, N'30', N'20 PRE', N'2A' ),
( 3, N'40', N'30 PRE', N'3A' ),
( 4, N'50', N'40 PRE', N'4A' ),
( 5, N'60', N'50 PRE', N'5A' ),
( 6, N'70', N'60 PRE', N'6A' ),
( 7, N'80', N'70 PRE', N'7A' ),

( 8, N'100', N'90 PRE', N'9A' ),
( 9, N'110', N'100 PRE', N'10A' ),
( 9, N'120', N'110 PRE', N'11A' )

INSERT  INTO #Item VALUES  
( 1, N'10', N'10 DESC' ),
( 2, N'20', N'20 DESC' ),
( 3, N'30', N'30 DESC' ),
( 4, N'40', N'40 DESC' ),
( 5, N'50', N'50 DESC' ),
( 6, N'60', N'60 DESC' ),
( 7, N'70', N'70 DESC' ),
( 8, N'80', N'80 DESC' ),

( 9, N'90', N'90 DESC' ),
( 10, N'100', N'100 DESC' ),
( 11, N'110', N'110 DESC' )

Output:

enter image description here

I can't get the other approach to give me exactly what i want so i'm going to try and adapt yours... — jhowe, May 20 '15 at 10:17
I don't think the joins are right... i'm just getting lots of ItemID's next to RecIDs, all other columns are null... if BOM joins to BOMVERSION through BOMID, what should the joins be at the bottom? — jhowe, May 20 '15 at 10:32
Without some sample data I can not answer. I have added some rows to your sample data to get child records and this query was working as I have shown in output. — Giorgi Nakeuri, May 20 '15 at 10:51
Hi Giorgi, now I understand everything better i'm going to try and redo some data and query. Sorry about this I just haven't been able to get it right. — jhowe, May 20 '15 at 12:55
@jhowe, I have added some test data that I think is correct parent chaild data and you can see output if you open it in new tab to fullscreen. — Giorgi Nakeuri, May 20 '15 at 13:54
in bom you have `100002 1A`, in bom version you have `100002 1A` . Isn't this cyclic reference? in bom you have 100001 3 times, in output you have 5 times. Are you kidding man? \ — Giorgi Nakeuri, May 20 '15 at 14:28
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/78334/discussion-between-jhowe-and-giorgi-nakeuri). — jhowe, May 20 '15 at 14:30
@jhowe, I guess you have managed to convert the query to your need. I am glad that it was helpful. — Giorgi Nakeuri, May 21 '15 at 14:50

SQL Recursive CTE 'where-used' / BOM explosion part 2

2 Answers2