-2

Stuck on this hierarchy problem, so here goes. I have the following hierarchy table, which has been truncated.

HierarchyIDs

The hierarchy has been joined to another table on the 'codes' column, with the following sample result. Let's call this table1.

table with hierarcjy

For each distinct ID in table1, I want to utilize the hierarchy to find a parent, if it exists. For example, under the codes column, 18 is the parent of 19. I would like to fold or group the 19 with 18 and eliminate that row. The desired result is something like this.

desired result

This does sort of what I want, using the GetAncestor method:

SELECT A.OrgNode, A.Codes, A.ID, B.OrgNode, B.Codes, B.ID FROM table1 A INNER JOIN table1 B ON A.OrgNode.GetAncestor(1) = B.OrgNode WHERE A.ID = B.ID
user2923767
  • 567
  • 1
  • 4
  • 13
  • 2
    I think you had better post some expected results as I cant understand your description – TomC Jun 28 '19 at 05:57
  • I can't figure out how get to these desired results from this sample data. Can you explain how you, as a human, get the values in your second row of your desired result? It feels totally arbitrary. You say "I want to apply the HierarchyID" but what does it mean to "Apply" a "HierarchyID" and what is a "HierarchyID" as no such column exists in your data? You then say "with the parent for those codes" Where is a "Parent" in this data, what "Codes"? – JNevill Jun 28 '19 at 12:21
  • Sorry. Poorly written on my part. Updated question. – user2923767 Jun 28 '19 at 14:05

1 Answers1

0

This self-join, with the GetAncestor function, did what I wanted it to do, with the 'where' on the same ID.

SELECT A.OrgNode, A.Codes, A.ID, B.OrgNode, B.Codes, B.ID FROM table1 A INNER JOIN table1 B ON A.OrgNode.GetAncestor(1) = B.OrgNode WHERE A.ID = B.ID

user2923767
  • 567
  • 1
  • 4
  • 13