7

I'm trying to find a way to get all ancestor nodes of a given node using HierarchyID. Every solution I've seen using HierarchyID seems to use either a CTE or a variable. Is there a way to do this using a single select statement?

To make things simpler:

CREATE TABLE Employee
(
    EmpId INT PRIMARY KEY IDENTITY,
    EmpName VARCHAR(100) NOT NULL,
    Position HierarchyID NOT NULL
)

INSERT INTO Employee (EmpName, Position)
VALUES ('CEO', '/'),
    ('COO', '/1/'),
    ('CIO', '/2/'),
    ('CFO', '/3/'),
    ('VP Financing', '/3/1/'),
    ('Accounts Receivable', '/3/1/1/'),
    ('Accountant 1', '/3/1/1/1/'),
    ('Accountant 2', '/3/1/1/2/'),
    ('Accountant 3', '/3/1/1/3/'),
    ('Accounts Payable', '/3/1/2/'),
    ('Accountant 4', '/3/1/2/1/'),
    ('Accountant 5', '/3/1/2/2/'),
    ('DBA', '/2/1/'),
    ('VP of Operations', '/1/1/')
2
  • a CTE solution is a single select statement, isn't it? – Jack Douglas Aug 8 '11 at 13:52
  • Generally, the solutions I've seen using CTEs are recursive, which is confusing. Also, there's a select to define the CTE (rather two for the recursion) and one to pull the data from it. I was wondering if there is a single statement that could be made without using CTEs. – Richard Aug 8 '11 at 13:55
10

To get "all parent nodes of a given node":

select *, position.GetAncestor(1), position.GetAncestor(1).ToString()
from employee
where position=hierarchyid::Parse('/3/1/')

EmpId  EmpName       Position  (No column name)  (No column name)
5      VP Financing  0x7AC0    0x78              /3/

but there will only ever be one due to the nature of hierarchies.

If you really want to get all immediate children nodes of a given node:

select * 
from employee 
where position.IsDescendantOf(hierarchyid::Parse('/3/1/'))=1
      and position.GetLevel()=hierarchyid::Parse('/3/1/').GetLevel()+1

EmpId  EmpName              Position
6      Accounts Receivable  0x7AD6
10     Accounts Payable     0x7ADA

-- EDIT

I see that you want all ancestor nodes. Perhaps try an approach like this:

select * 
from employee
where hierarchyid::Parse('/3/1/2/1/').IsDescendantOf(Position) = 1

or

select * from employee
where ( select position 
        from employee 
        where empname='Accountant 4' ).IsDescendantOf(Position) = 1

here is a CTE method for comparison:

with w as ( select * from employee where empname='Accountant 4'
            union all
            select e.*
            from employee e join w on(w.position.GetAncestor(1)=e.Position) )
select * from w;
1
  • I really like those. They're very clean and easy to understand. Much better than what I was coming up with. – Richard Aug 9 '11 at 14:00
0
DECLARE @hid_Specific HIERARCHYID 

/** Assign your specific node path **/
SET @hid_Specific='/1/1/3/1/';

SELECT 
     hrchy_id,
     * 
FROM 
     tblHierarchyData 
WHERE 
     PATINDEX(hrchy_id.ToString()+'%',@hid_Specific.ToString())=1
2
  • please consider registering your account - that will allow you to login from multiple locations and allows you to regain access to your account if you forget the password, etc. Also, please take the tour - thanks! – Hannah Vernon Dec 31 '20 at 15:23
  • While code is almost always helpful, it's great if you can explain how your solution works, or why it's a good way to go. Adding a link or two to the official documentation can help as well. – Hannah Vernon Dec 31 '20 at 15:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.