• Jagan Mohan Reddy Rajidi

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





108 views0 comments

Recent Posts

See All

When you create any new object(table, stored procedure, view,...etc.) in database then same object name will be stored in system table Sysobjects. Following query returns all the object names along wi

Sometimes you get a scenario where you know the column name but don't remember which table it belongs to. Following query will search for a given column name in database schema and returns all objects