Hi @f_kefer ,

how to approach this depends on what your exact needs are. If you know the exact number of levels in your hierarchy or have a low upper-limit on the number of levels you can usually get things set up quite easily: https://stackoverflow.com/questions/68865866/translate-a-table-with-parent-child-hierarchy-into-a-table-with-parent-name-in or https://stackoverflow.com/questions/45661314/how-to-create-query-from-parent-child-hierarchy-table/45661741?noredirect=1#comment121704882_45661741 should give you some ideas.

I tend to deal with situations where you do not know the depth of the hierarchy and it may change over time, so I tend to use recursive CTE's. Those get into “it depends” territory pretty quickly for exact syntax, but this is an example for inspiration: https://stackoverflow.com/questions/63897337/flattening-of-hierarchy-in-sql-server-dynamically

There are some nice tricks with CROSS APPLY, PIVOT/UNPIVOT and FOR XML syntax possible that allow you to derive the Level X column from a “path” you build up out of the ancestry of an item. Whichever you choose will depend on sizes and complexity, i.e. sometimes you get ranges of elements pointing to parents or you have explicit ordering between siblings. 

Hi @f_kefer 

Did Rory’s answer above, help answer your question? If so, please help us by marking a best answer above. If you have follow up questions please let us know

Hi @rory.smith,

thanks for your advice but I solved this task a little bit different.

My solution was to flat the parent-child table rowwise, so the transformed table looks something like this:

name, cost_center
management, 1
management, 2
management, 3
management, 4
teamlead_1, 2
teamlead_1, 4
…

Then I used this table to set up a Dynamic Row-Level Security on the cost_center column of the table I want to filter.
I wasn’t aware that TimeXtender supports this kind of flattened table for dynamic RLS until I tried it.