CTE Create product hierarchy tree

Question

I have the following three tables representing product data. To briefly explain, products "A" and "B" are end products. To find out the parts that make product "A", we look at the "ProductComponents" table giving us the ComponentListId = 1.

Querying this ComponentListId against the "PartsSubcomponents" table tells us that it has two sub-components i.e. A11 and A12.

A11 is is small as it gets and it has no further sub-components. However, A12 exists in the "ProductComponents" table telling us that it is made with X1 and X2.

EndProducts

EndProductId
A
B
...

ProductComponents

ProductId ComponentListId
A         1
A12       99
...

PartsSubcomponents

ComponentListId SubComponentId
1               A11
1               A12
99              X1
99              X2
...

I need to use CTE to find the hierarchy levels between products and their parts. In this case, the result should be like this:

EndProductId,ProductId,ComponentListId,SubcomponentId,Level
A,           A,        1,              A11,           L1
A,           A,        1,              A12,           L1
A,           A12,      99,             X1,            L2
A,           A12,      99,             X2,            L2

I though of writing a console app but that got really dirty very soon. Wasn't really able to map Internet examples for CTE to my problem but I still believe that it would be the best way to do it. — Syed, Feb 07 '17 at 09:31

score 2 · Accepted Answer · answered Feb 07 '17 at 09:49

Here's a simple recursive cte that does what you're after and produces your desired output:

CREATE TABLE #EndProducts
    (
      EndProductId NVARCHAR(1)
    );

INSERT  INTO #EndProducts
        ( EndProductId )
VALUES  ( 'A' ),
        ( 'B' );

CREATE TABLE #ProductComponents
    (
      ProductId NVARCHAR(3) ,
      ComponentListId INT
    );

INSERT  INTO #ProductComponents
        ( ProductId, ComponentListId )
VALUES  ( 'A', 1 ),
        ( 'A12', 99 );

CREATE TABLE #PartsSubcomponents
    (
      ComponentListId INT ,
      SubComponentId NVARCHAR(3)
    ); 

INSERT  INTO #PartsSubcomponents
        ( ComponentListId, SubComponentId )
VALUES  ( 1, 'A11' ),
        ( 1, 'A12' ),
        ( 99, 'X1' ),
        ( 99, 'X2' );

WITH    cte
          AS ( -- anchor member gets level 1
               SELECT   e.EndProductId ,
                        pc.ProductId ,
                        sc.ComponentListId ,
                        sc.SubComponentId ,
                        1 AS [Level]
               FROM     #EndProducts e
               INNER JOIN #ProductComponents pc 
                     ON e.EndProductId = pc.ProductId
               INNER JOIN #PartsSubcomponents sc 
                     ON pc.ComponentListId = sc.ComponentListId
               UNION ALL
               -- recursive member gets the additional data and increments levels
               SELECT   cte.EndProductId ,
                        cte.SubComponentId AS ProductId ,
                        pc.ComponentListId ,
                        sc.SubComponentId ,
                        cte.[Level] + 1 AS [Level]
               FROM     cte
               INNER JOIN #ProductComponents pc 
                     ON cte.SubComponentId = pc.ProductId
               INNER JOIN #PartsSubcomponents sc 
                     ON pc.ComponentListId = sc.ComponentListId
             )
    SELECT  *
    FROM    cte;

DROP TABLE #EndProducts;
DROP TABLE #PartsSubcomponents;
DROP TABLE #ProductComponents;

Result:

EndProductId    ProductId   ComponentListId SubComponentId  Level
A               A           1               A11             1
A               A           1               A12             1
A               A12         99              X1              2
A               A12         99              X2              2

First of all, thank you. This works like a charm! and secondly, could you point me to good resources which I could follow to avoid newbie questions in the future? — Syed, Feb 07 '17 at 10:14

CTE Create product hierarchy tree

1 Answers1