I have the following data (a sample of real data, which is much bigger):
id p_id
1 null
2 1
3 1
4 2
5 4
6 5
7 5
8 3
9 7
Would it be possible to use analytic function of SQL Server to get the following result (get the deepest hierarchy):
id p_id
1 null
2 1
4 2
5 4
7 5
9 7
I can use Recursive CTE to get the level of each ID, however I'm trying to use analytic functions instead. I suspect I need to use Lag or Lead function, however I am new to analytic functions so if this is possible to be done, please help share the solution. Your help is appreciated! Thanks.
Let me post this as an answer rather than as a comment.
No, you cannot use analytic functions for this purpose. Analytic functions let you summarize data about known groups of records. The summaries can be quite exotic -- such as partial sums or calculating percentiles or fetching the previous record in the group. However, the "group" is defined statically in the windowing clause.
That said, it is possible to represent the hierarchical data so this is possible. The idea is to include the full path for each element:
id parent_path
1 /1
2 /1/2
3 /1/3
4 /1/2/4
5 /1/2/4/5
6 /1/2/4/5/6
7 /1/2/4/5/7
8 /1/3/8
9 /1/2/4/5/7/9
Using this representation, you can use window functions to get all the children of "1". That said, window functions are not needed at all. A simple like expression works: parent_path like '/1/%'.
The challenge with this data structure is setting up the triggers to maintain it (definitely possible, just a bit of work).