How to form Management Hierarchy Formation in SQL Server?

Question

how to form the management hierarchy for below records?

Input Data:

Id	Sub ID	Name	Description
101	NULL	Page Reference	Page Reference
102	1	Page 1	Page 1
103	2	Ashok	Ashok
104	3	Kumar	Kumar
105	4	Page 2	Page 2
106	5	Arvind	Arvind
107	4	Page 11	Page 11
108	6	Gova	Gova
109	7	Gokul	Gokul
110	8	Kannan	Kannan

I tried with Recursion CTE, I am not able to find exact solution. Need an below format, Conditions are

New Leaf ID --> If Sub ID IS NULL , then it will be 1 , If contains page row, it will be 2, if contains other than that it will be 3.

Page --> Whenever Page row starts, from the next row original information showing for that page. we need to form based on the lead rows.

Output Data:

Id	Sub ID	Name	Page	New Leaf ID
101	NULL	Page Reference		1
102	1	Page 1	Page 1	2
103	2	Ashok	Page 1	3
104	3	Kumar	Page 1	3
107	4	Page 11	Page 11	2
108	6	Gova	Page 11	3
109	7	Gokul	Page 11	3
110	8	Kannan	Page 11	3
105	4	Page 2	Page 2	2
106	5	Arvind	Page 2	3

GMB GMB · Accepted Answer · 2020-12-30T14:55:31

The new leaf id is conditional logic. As for the page, it is a bit trickier: it seems like the ordering of the rows defines the dependencies, so basically you want to relate each level 3 leaf to the preceding level 2.

Here is an approach using a window count to identify leafs that belong to the same page:

select id, subid, name, 
    case when sub_id is not null
        then max(case when name like 'Page %' then name end) over(order by id) 
    end as page,
    case 
        when subid is null then 1
        when name like 'Page %' then 2
        else 3
    end as new_leaf_id
from (
    select t.*,
        sum(case when name like 'Page %' then 1 else 0 end) over(order by id) grp
    from mytable t
) t

How to form Management Hierarchy Formation in SQL Server?

1 Answers