sql-serverparent-childhierarchyrecursive-querydimensional

Binding a mutltiple parent hierarchy


I have a table where informantion about some branches and hierarchies are kept. The information are not in stored in a way that can be used in dimensional modelling, so after many transformations and extractation I end up with this:

Hierarchy

where n0 it's the first level and the n11 ist the highest level. The problem is that the hierarchy cannot be travers like this:

SELECT 
    distinct 
        nelem.Element n0,
        primul.Element n1,
        doilea.Element n2, 
        treilea.Element n3,
        patrulea.Element n4 

FROM 
    [MIS].[dbo].[BedrockImportBST] nelem
    left join  [BedrockImportBST] primul on primul.Element=nelem.Value1 and primul.LineType in ('e','p')
    left join  [BedrockImportBST] doilea on doilea.Element=primul.Value1 and doilea.LineType in ('e','p')
    left join  [BedrockImportBST] treilea on treilea.Element=doilea.Value1 and treilea.LineType in ('e','p')
    left join  [BedrockImportBST] patrulea on patrulea.Element=treilea.Value1 and patrulea.LineType in ('e','p')

Is there a posibility to assign parent-child realtionship ids to this solution so that the traversing would be recursiv?


Solution

  • Then just for transforming the flat data into a parent-child hierarchy, you could generate unique IDs with simple unions like below (I used FlatHierarchy as the name of your table). I see that a specific element can appear on multiple levels and can have different parents, which seems a bit strange, but here it goes:

    DECLARE @IDS TABLE
    (
        ID INT IDENTITY (1,1) NOT NULL,
        Label varchar(50)
    )
    INSERT INTO @IDS (Label)
    SELECT n0
    FROM FlatHierarchy
    UNION 
    SELECT n1
    FROM FlatHierarchy
    UNION 
    SELECT n2
    FROM FlatHierarchy
    UNION 
    SELECT n3
    FROM FlatHierarchy
    UNION 
    SELECT n4
    FROM FlatHierarchy
    UNION 
    SELECT n5
    FROM FlatHierarchy
    .....
    UNION 
    SELECT n11
    FROM FlatHierarchy
    
    
    SELECT i.ID, parent.ID ParentID, i.Label
    FROM @IDS i
    INNER JOIN FlatHierarchy f ON i.Label = f.n0
    LEFT JOIN @IDS parent ON f.n1 = parent.Label
    UNION 
    SELECT i.ID, parent.ID ParentID, i.Label
    FROM @IDS i
    INNER JOIN FlatHierarchy f ON i.Label = f.n1
    LEFT JOIN @IDS parent ON f.n2 = parent.Label
    UNION 
    SELECT i.ID, parent.ID ParentID, i.Label
    FROM @IDS i
    INNER JOIN FlatHierarchy f ON i.Label = f.n2
    LEFT JOIN @IDS parent ON f.n3 = parent.Label
    UNION 
    SELECT i.ID, parent.ID ParentID, i.Label
    FROM @IDS i
    INNER JOIN FlatHierarchy f ON i.Label = f.n3
    LEFT JOIN @IDS parent ON f.n4 = parent.Label
    UNION 
    SELECT i.ID, parent.ID ParentID, i.Label
    FROM @IDS i
    INNER JOIN FlatHierarchy f ON i.Label = f.n4
    LEFT JOIN @IDS parent ON f.n5 = parent.Label
    .....
    UNION 
    SELECT i.ID, parent.ID ParentID, i.Label
    FROM @IDS i
    INNER JOIN FlatHierarchy f ON i.Label = f.n10
    LEFT JOIN @IDS parent ON f.n11 = parent.Label