Hierarchy For CTE In MS SQL

Hello everybdoy,

today I want to write some notes about hierarchy research in ms sql database. 

Imagine you have following structure in your table 

TableWithParentChild:

id               |           rootID   |     some additional fields

1                |            1          |      ----

2                |            1          |      ----

3                |            2          |      ----

4                |            2          |      ----

5                |            2          |      ----

and image that you need to get info like who is below 1. 

for this purpose following sql can be useful:

-- wrap up tree selection into tvf
create function HierarchyFunction(@parentID int)
returns table
as
return
with tree
as
(select id,  rootID from TableWithParentChild
         where id = @parentID
         union all
         select p.id, p.rootID from TableWithParentChild as P
         join tree on tree.id = P.rootID
         and P.id != P.rootID
)
select * from tree

select p.* from  TableWithParentChild as p
join HierarchyFunction(1) as tree
on p.id = tree.id

Ready to take your Acumatica development to the next level? Just like we explored hierarchical data structures in SQL to unlock deeper insights, your business can achieve greater efficiency and customization with tailored Acumatica solutions. If you have a specific customization need or a unique business challenge, don’t hesitate to reach out! Leave a request for a customization today, and let’s build something extraordinary together. Your ideal solution is just a click away—let’s make it happen!