Example of storage and query for MSSQL server tree structure

Example of storage and query for MSSQL server tree structure
Database Tutorial design often encounter the need to store tree structure, such as employee Relationship table, organization chart, and so on.

SQL code

--Test data
CREATE TABLE #employees (
Employeecode varchar NOT NULL primary key clustered,
Reporttocode varchar () 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

/*
Query problems that you may encounter:
1. All direct subordinates of employee ' d '
2. Employees ' d ' all subordinates within level 2 (including direct subordinates and direct subordinates)
3. All superiors of employee ' N ' (listed in order of report line)
4. Staff @employeecode all @leveldown level subordinates (@employeecode and @leveldown are passed in by variable)
DECLARE @employeecode varchar (m), @leveldown int;
Set @employeecode = ' d ';
Set @leveldown = 2;
5. Staff @employeecode of all @levelup level within the superior (@employeecode and @levelup to pass the variable)
DECLARE @employeecode varchar (m), @levelup int;
Set @employeecode = ' n ';
Set @levelup = 2;
*/
--Using a recursive CTE to implement the Employee tree relationship table

With CTE as (
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 Reporttopath