Hello C# legends,
I have raw data with a family structure vertical as below
We want to build parent-children relationships from the above structure to horizontal using ClientID as keys as per below.
The logic is the parent is the closest one level higher hierarchy order by ROW_N.
Hence expected output is below
SQL gurus say this is too complex for SQL hence I was wondering whether you guys could help the logic to build in C#?
I have raw data with a family structure vertical as below
Code:
ROW_N FamilyID CompanyName HierarchyLevel ClientID
1 A XX1 0 A1
2 A XX2 1 A12
3 A XX3 2 A345
4 A XX4 2 A456
5 A XX5 3 A6788
6 A XX6 2 A346
7 A XX7 3 A6789
8 B XX8 0 B1
9 B XX9 1 B11
10 B X10 2 B345
11 B X11 2 B567
12 B X12 3 B6789
We want to build parent-children relationships from the above structure to horizontal using ClientID as keys as per below.
The logic is the parent is the closest one level higher hierarchy order by ROW_N.
Hence expected output is below
Code:
ROW_N FamilyID ParentID ParentName ChildrenID ChildrenName HierarchyLevel
1 A A1 XX1 A12 XX2 1
2 A A12 XX2 A345 XX3 2
3 A A12 XX2 A456 XX4 2
4 A A456 XX4 A6788 XX5 3
5 A A12 XX2 A346 XX6 2
6 A A346 XX6 A6789 XX7 3
7 B B1 XX8 B11 XX9 1
8 B B11 XX9 B345 X10 2
9 B B11 XX9 B567 X11 2
10 B B567 X11 B6789 X12 3
SQL gurus say this is too complex for SQL hence I was wondering whether you guys could help the logic to build in C#?