First
of all let us create a table called Employee table using the below script.
INSERT INTO
Employee
VALUES
(5,
'B_B'
,2)
INSERT INTO
Employee
VALUES
(9,
'BB_B'
,5)
INSERT INTO
Employee
VALUES
(11,
'BC_B'
,6)
INSERT INTO
Employee
VALUES
(13,
'BBB_B'
,9)
--Select the root or parent records
SELECT
FROM
Employee
WHERE
Manager_ID
IS
NULL
UNION
ALL
SELECT
FROM
Employee e
INNER JOIN
DirectReports d
ON
e.Manager_ID
=
d.Employee_ID
INNER JOIN
employee m
ON
e.manager_ID
=
m.employee_id
)
(Manager_ID
INT
,
ManagerName
VARCHAR
(100),
Employee_ID
INT
,
EmployeeName
VARCHAR
(100) ,
EmployeeLevel
INT
)
--Selecting the 0th level (who do not have
manager) employee
SELECT
Manager_ID,
CAST
(
''
AS VARCHAR
(100))
AS
ManagerName,
Employee_ID, EmployeeName ,
0
AS
EmployeeLevel
FROM
Employee
WHERE
Manager_ID
IS
NULL
WHILE
(
@@ROWCOUNT
>0)
BEGIN
SET
SET
@Recursion
=
@Recursion
+1
INSERT INTO
@DirectReports
SELECT
e.Manager_ID,
m.EmployeeName
AS
ManagerName,
e.Employee_ID,
e.EmployeeName ,
@Recursion
FROM
Employee e
INNER JOIN
@DirectReports
d
ON
e.Manager_ID
=
d.Employee_ID
INNER JOIN
employee m
ON
e.manager_ID
=
m.employee_id
WHERE
d.EmployeeLevel
=
@Recursion
-1
--to select only the last execution result
END
SELECT
SELECT
*
FROM
@DirectReports
CREATE
TABLE Employee
(Employee_id
INT PRIMARY KEY,
EmployeeName
VARCHAR(100),
Manager_id
INT
)
Let us populate the
sample data.
INSERT INTO
Employee
VALUES
(1,
'A'
,NULL)
INSERT INTO
Employee
VALUES
(2,
'A_B'
,1)
INSERT INTO
Employee
VALUES
(3,
'A_C'
,1)
INSERT INTO
Employee
VALUES
(4,
'A_D'
,1)
INSERT INTO
Employee
VALUES
(6,
'B_C'
,2)
INSERT INTO Employee VALUES (7,'C_B',3)
INSERT INTO
Employee
VALUES
(8,
'C_C'
,3)
INSERT INTO
Employee
VALUES
(10,
'BB_C'
,5)
INSERT INTO
Employee
VALUES
(12,
'BC_C'
,6)
INSERT INTO
Employee
VALUES
(14,
'BBC_B'
,10)
I have
used separate insert statement for better readability.
Here
Manager_id is a foreign key referring to Employee_id.
Let us
assume that we need to generate an employee reports with Employee_id,Employee
Name ,Manager_id,Manager Name and hierarchical position (level) of
employee in the organization. This can be implemented very easily using
recursive CTE which introduced in SQL server 2005.Below script will give the
result.
;WITH
DirectReports(Manager_ID, ManagerName,Employee_ID, EmployeeName ,EmployeeLevel)
AS
(
Manager_ID,
CAST
(
''
AS VARCHAR
(100))
AS
ManagerName,
Employee_ID,
EmployeeName,
0
AS
EmployeeLevel
--Recursive part :Select the child
e.Manager_ID,
m.EmployeeName
AS
ManagerName,
e.Employee_ID,
e.EmployeeName,
EmployeeLevel + 1
SELECT * FROM DirectReports ;
Let us
see how we can write the same with out CTE, which will help us to visualize the
recursive CTE.
DECLARE
@DirectReports
AS TABLE
INSERT INTO
@DirectReports
DECLARE
@Recursion
INT =
0
No comments:
Post a Comment