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:
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?
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