I have this:
N_LEVEL saves parent_child relations level, N_LEFT and N_RIGHT columns are ranges of parent_child relations.
How can I select data with hierarchy as shown below:
--create table roles ( id int not null, parentId int, roleName varchar(50) not null );
DECLARE @roles TABLE(id int not null,
N_LEFT int,
N_RIGHT int,
N_LEVEL int,
DISPLAY_NAME varchar(50))
insert into @roles
(id, N_LEFT, N_RIGHT,N_LEVEL,DISPLAY_NAME)
values
(1, 97 , 120 , 1 , 'Students'),
(2, 98 , 113 , 2 , 'Bachelors'),
(3, 114 , 115 , 2 , 'Masters'),
(4, 105 , 106 , 3 , '2020' ),
(5, 99 , 102 , 3 , '2018'),
(6, 118 , 119 , 2 , 'PhD-DSc'),
(7, 103 , 104 , 3 , '2019'),
(8, 116 , 117 , 2 , 'TKDorm'),
(9, 107 , 108 , 3 , '2021'),
(10, 109 , 110 , 3 , '2021Add'),
(11, 111 , 112 , 3 , '2022')
select * from @roles