CTE Recursive query for data hierarchy(Parent Child hierarchy)

Create a sample table Employee with some data as shown below to learn about CTE Recursive query used for data hierarchy.

CREATE TABLE Employee(ID int,Name varchar(30),ManagerID INT)

INSERT INTO Employee VALUES(1,'Steve',NULL)
INSERT INTO Employee VALUES(2,'Andrew',1)
INSERT INTO Employee VALUES(3,'Mark',1)
INSERT INTO Employee VALUES(4,'Smith',2)
INSERT INTO Employee VALUES(5,'Richards',4)
INSERT INTO Employee VALUES(6,'Bob',3)
INSERT INTO Employee VALUES(7,'Jobs',5)

Following CTE Recursive query provides all the employees under 'Andrew' whose employee ID is "2"(Data hierarchy for 'Andrew').

WITH CTE 
AS(
SELECT ID,Name,ManagerID, 1 RecursiveCallNumber  FROM Employee  WHERE ID=2
UNION ALL
SELECT  E.ID,E.Name,E.ManagerID,RecursiveCallNumber+1 RecursiveCallNumber  FROM Employee E
INNER JOIN CTE ON E.ManagerID=CTE.ID)
SELECT * FROM CTE

Output

SHARES

Share

Pin it

Tweet

Share

Share

Share

Buffer

Print

About Mariano

I'm Ethan Mariano a software engineer by profession and reader/writter by passion.I have good understanding and knowledge of AngularJS, Database, javascript, web development, digital marketing and exploring other technologies related to Software development.