Sql Server grouping products from a table with hierarchical relationship based on parent node

Question

I have a table with products in hierarchical relationship - for example there is row with bag of apples containing 5kg apples and a child row with 1 kg apples). This relationship is given like below:

Id   ContainsId      Name

8792    4567     Bag - 5kg apples
4567    NULL        1 kg apples

What I need, is to group the products from each hierarchical family, so that I could display them in a list next to each other. The result should look like below:

 FamilyId   Id        Name
   8792    8792   Bag - 5kg apples
   8792    4567   1kg apples

The family can have more than two members.

Could you please help me ?

Answer 1

Using a Recursive CTE this can be done like so

DECLARE @tbl TABLE (Id INT, ContainsId INT, [Name] NVARCHAR(100))
INSERT INTO @tbl
(Id, ContainsId, [Name])
VALUES
(8792,4567,'Bag - 5kg apples')
,(4567,5555,'1 kg apples')
,(5555,7878,'Some Oranges') --Added some extra items
,(7878,NULL,'Fish Fingers') --Added some extra items

;WITH cteX
AS
(   SELECT
        T.Id, T.ContainsId,  T.[Name], FamilyId = T.Id
    FROM
        @tbl    T
    WHERE
        T.Id = 8792
    UNION ALL
    SELECT
          T1.Id
        , T1.ContainsId
        , T1.[Name]
        , R.FamilyId
    FROM
        @tbl        T1
    INNER JOIN
        cteX        R ON    R.ContainsId = T1.Id
)
SELECT T.FamilyId
     , T.Id
     , T.Name
FROM cteX T

Output

FamilyId    Id      Name
8792        8792    Bag - 5kg apples
8792        4567    1 kg apples
8792        5555    Some Oranges
8792        7878    Fish Fingers