Storage and query instances in the mssqlserver Tree Structure

In the storage and query instance design of the mssqlserver tree structure, tree structures such as employee relationship tables and organizational structure tables are often stored. Sqlcode -- Test Data createtable # employees (& nbsp; employeecodevarchar (20) notnullprimarykeyclustered, & nbsp; storage and query instances in the mssql server Tree Structure
Tree structures, such as employee relationship tables and organizational structure tables, are often stored in the design.

SQL code

-- Test Data
Create table # employees (
Employeecode varchar (20) not null primary key clustered,
Reporttocode varchar (20) null)
Go
Insert into # employees values ('A', null)
Insert into # employees values ('B', 'A ')
Insert into # employees values ('C', 'A ')
Insert into # employees values ('D', 'A ')
Insert into # employees values ('E', 'B ')
Insert into # employees values ('F', 'B ')
Insert into # employees values ('G', 'C ')
Insert into # employees values ('h', 'D ')
Insert into # employees values ('I', 'D ')
Insert into # employees values ('J', 'D ')
Insert into # employees values ('k', 'J ')
Insert into # employees values ('l', 'J ')
Insert into # employees values ('M', 'J ')
Insert into # employees values ('n', 'k ')
Go

/*
Possible query problems:
1. All direct subordinates of employee 'D'
2. All subordinates of employee 'D' Level 2 (including direct subordinates and direct subordinates)
3. All superiors of employee 'n' (listed in the report line Order)
4. Employees @ employeecode all subordinates within the @ leveldown level (@ employeecode and @ leveldown are passed in as variables)
Declare @ employeecode varchar (20), @ leveldown int;
Set @ employeecode = 'D ';
Set @ leveldown = 2;
5. Employees @ employeecode all superiors within the @ levelup level (@ employeecode and @ levelup are passed in as variables)
Declare @ employeecode varchar (20), @ levelup int;
Set @ employeecode = 'n ';
Set @ levelup = 2;
*/
-- Implement employee tree relationship table with recursive cte

With cte (
Select
Employeecode,
Reporttocode,
Reporttodepth = 0,
Reporttopath = cast ('/' + employeecode + '/' as varchar (200 ))
From # employees
Where reporttocode is null
Union all
Select
E. employeecode,
E. reporttocode,
Reporttodepth = mgr. reporttodepth + 1,
Reporttopath = cast (mgr. reporttopath + e. employeecode + '/' as varchar (200 ))
From # employees e
Inner join cte mgr
On e. reporttocode = mgr. employeecode
)
Select * from cte order by reporttopath