CODEWITHSUNDEEP
sql-servercommon-table-expressionrecursive-cte
Syed
Syed

Reputation: 358

CTE Create product hierarchy tree

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

Upvotes: 1

Views: 601

Answers (1)

Tanner
Tanner

Reputation: 22753

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

Upvotes: 2

Related Questions