Selecting all Ancestors in the order of relation from hierarchical table SQL

Question

I've a table like this with a parent child relation in the same table in SQL Server

Finished Product|   Quantity           Unit                Bill Of Material
----------------------------------------------------------------------------------------
Product 1             1                 Nos                    Product 2
Product 1             1                 Nos                    Product 3
Product 1             1                 Nos                    Product 4
Product 2             2                 Nos                    Product 5
Product 2             1                 Nos                    Product 6
Product 2             3                 Nos                    Product 7
Product 2             1                 Nos                    Product 8
Product 3             3                 Nos                    Product 9
Product 3             2                 Nos                    Product 10
Product 3             1                 Nos                    Product 5
Product 3             1                 Nos                    Product 6
Product 4             2                 Nos                    Product 5
Product 4             2                 Nos                    Product 7
Product 4             2                 Nos                    Product 8
Product 5             2                 Nos                    Product 6
Product 5             3                 Nos                    Product 7
Product 5             4                 Nos                    Product 11
Product 5             2                 Nos                    Product 12

So when I check the Material "Product 6" how related to the final product "Product 1" I have to get the tables in the order below.


      Quantity  Unit        Reference Item
        --------------------------------------------
        6           Nos      Product 5
        1           Nos      Product 3
        1           Nos      Product 2

Since "Product 6" is not directly connected to "Product 1" but indirectly connected through "Product 1" child.

why result table contain 6 Nos for "Product 5" is that "Product 6" is connected to "Product 1" trough "Product 5" in 3 different ways. and we need 2 nos of "product 6" for each "product 5" i.e

Product 1 ---- Product 2 ---- Product 5 ---- product 6.<br/>
Product 1 ---- Product 3 ---- Product 5 ---- Product 6.<br/>
Product 1 ---- Product 4 ---- Product 5 ---- product 6.<br/>

the above sequences only consider "Product 5" relation.

it s actually a table of BOM. That means an assembled product BOM. The Final "Product 1" is derived from three products and each product is derived from others in a hierarchical structure. I need to find out a particular raw-material how related to his parent; means directly or by ancestrally? — Sabith Paul, May 01 '14 at 18:12
Sorry, the question is still not clear: In your comment you say "directly or by ancestrally". However, in the desired result table of your question you have only Products 5, 3 and 2, which are direct parents of Parent 6, not all ancestors. — helix, May 01 '14 at 22:40

Colin Frame · Answer 1 · 2014-05-02T10:01:19.773

You could use a recursive CTE. For all possible lines relating to finished Product 1, set @Material to NULL. Likewise, to see all lines for material Product 6, set @FinishedProduct to NULL.

-- Create CTE  
DECLARE @Material VARCHAR(50) = 'Product 6',  
@FinishedProduct VARCHAR(50) = 'Product 1'  

;WITH   recursiveProduct  
AS   
(  
   -- Generate component level line, effectively where "parent" is null  
    SELECT  p1.Material,  
            CAST(( p1.FinishedProduct + ' --- ' + p1.Material ) AS VARCHAR(MAX)) FinishedProductList,  
            p1.FinishedProduct  
    FROM    dbo.Products p0  
    RIGHT JOIN dbo.Products p1 ON p0.FinishedProduct = p1.Material  
    WHERE   p0.Material IS NULL AND COALESCE(@Material,p1.Material) = p1.Material  

   UNION ALL

    -- Now the recursive bit
   SELECT   p2.Material,  
            CAST(( p2.FinishedProduct + ' --- ' + rp.FinishedProductList ) AS VARCHAR(MAX)) FinishedProductList,
            p2.FinishedProduct  
   FROM     recursiveProduct rp JOIN dbo.Products p2 ON rp.FinishedProduct = p2.Material  
 )  
-- Select data of interest  
SELECT FinishedProductList FROM recursiveProduct WHERE COALESCE(@FinishedProduct, FinishedProduct) = FinishedProduct

In case someone else wants to code for a better answer, here's code for generating test data. I'm assuming the Unit is irrelevent in this question.

IF OBJECT_ID('Products', 'U') IS NOT NULL  
    DROP TABLE Products  
GO  
CREATE TABLE Products 
( FinishedProduct VARCHAR(50) , Quantity INT , Material VARCHAR(50) )
GO
INSERT  dbo.Products
VALUES  ( 'Product 1', 1, 'Product 2' ),
        ( 'Product 1', 1, 'Product 3' ),
        ( 'Product 1', 1, 'Product 4' ),
        ( 'Product 2', 2, 'Product 5' ),
        ( 'Product 2', 1, 'Product 6' ),
        ( 'Product 2', 3, 'Product 7' ),
        ( 'Product 2', 1, 'Product 8' ),
        ( 'Product 3', 3, 'Product 9' ),
        ( 'Product 3', 2, 'Product 10' ),
        ( 'Product 3', 1, 'Product 5' ),
        ( 'Product 3', 1, 'Product 6' ),
        ( 'Product 4', 2, 'Product 5' ),
        ( 'Product 4', 2, 'Product 7' ),
        ( 'Product 4', 2, 'Product 8' ),
        ( 'Product 5', 2, 'Product 6' ),
        ( 'Product 5', 3, 'Product 7' ),
        ( 'Product 5', 4, 'Product 11' ),
        ( 'Product 5', 2, 'Product 12' )
GO

My required answer is the second table i mentioned in the question. Can you please modify the query for such a result? — Sabith Paul, May 04 '14 at 08:12
Sorry, missed your query at the time you posted. In answer, one way is simply to amend the last query commented as "Select data of interest" e.g. `SELECT FinishedProductList + '.
' FROM recursiveProduct WHERE COALESCE(@FinishedProduct, FinishedProduct) = FinishedProduct AND FinishedProductList LIKE '%Product 5%' ` — Colin Frame, Nov 26 '14 at 11:44

Selecting all Ancestors in the order of relation from hierarchical table SQL

1 Answers1