you are not providing any table structure, so here is an example of a CTE recursive processing of a tree structure:
--go through a nested table supervisor - user table and display the chain DECLARE @Contacts table (id varchar(6), first_name varchar(10), reports_to_id varchar(6)) INSERT @Contacts VALUES ('1','Jerome', NULL ) -- tree is as follows: INSERT @Contacts VALUES ('2','Joe' ,'1') -- 1-Jerome INSERT @Contacts VALUES ('3','Paul' ,'2') -- / \ INSERT @Contacts VALUES ('4','Jack' ,'3') -- 2-Joe 9-Bill INSERT @Contacts VALUES ('5','Daniel','3') -- / \ \ INSERT @Contacts VALUES ('6','David' ,'2') -- 3-Paul 6-David 10-Sam INSERT @Contacts VALUES ('7','Ian' ,'6') -- / \ / \ INSERT @Contacts VALUES ('8','Helen' ,'6') -- 4-Jack 5-Daniel 7-Ian 8-Helen INSERT @Contacts VALUES ('9','Bill ' ,'1') -- INSERT @Contacts VALUES ('10','Sam' ,'9') -- DECLARE @Root_id char(4) --get complete tree--------------------------------------------------- SET @Root_id=null PRINT '@Root_id='+COALESCE('''' +@Root _id+'''','null') ;WITH StaffTree AS ( SELECT c.id, c.first_name, c.reports_to_id, c.reports_to_id as Manager_id, cc.first_name AS Manager_first_name, 1 AS LevelOf FROM @Contacts c LEFT OUTER JOIN @Contacts cc ON c.reports_to_id=cc.id WHERE c.id=@Root _id OR (@Root_id IS NULL AND c.reports_to_id IS NULL) UNION ALL SELECT s.id, s.first_name, s.reports_to_id, t.id, t.first_name, t.LevelOf+1 FROM StaffTree t INNER JOIN @Contacts s ON t.id=s.reports_to_id WHERE s.reports_to_id=@Root _id OR @Root_id IS NULL OR t.LevelOf>1 ) SELECT * FROM StaffTree --get all below 2--------------------------------------------------- SET @Root_id=2 PRINT '@Root_id='+COALESCE('''' +@Root _id+'''','null') ;WITH StaffTree AS ( SELECT c.id, c.first_name, c.reports_to_id, c.reports_to_id as Manager_id, cc.first_name AS Manager_first_name, 1 AS LevelOf FROM @Contacts c LEFT OUTER JOIN @Contacts cc ON c.reports_to_id=cc.id WHERE c.id=@Root _id OR (@Root_id IS NULL AND c.reports_to_id IS NULL) UNION ALL SELECT s.id, s.first_name, s.reports_to_id, t.id, t.first_name, t.LevelOf+1 FROM StaffTree t INNER JOIN @Contacts s ON t.id=s.reports_to_id WHERE s.reports_to_id=@Root _id OR @Root_id IS NULL OR t.LevelOf>1 ) SELECT * FROM StaffTree --get all below 6--------------------------------------------------- SET @Root_id=6 PRINT '@Root_id='+COALESCE('''' +@Root _id+'''','null') ;WITH StaffTree AS ( SELECT c.id, c.first_name, c.reports_to_id, c.reports_to_id as Manager_id, cc.first_name AS Manager_first_name, 1 AS LevelOf FROM @Contacts c LEFT OUTER JOIN @Contacts cc ON c.reports_to_id=cc.id WHERE c.id=@Root _id OR (@Root_id IS NULL AND c.reports_to_id IS NULL) UNION ALL SELECT s.id, s.first_name, s.reports_to_id, t.id, t.first_name, t.LevelOf+1 FROM StaffTree t INNER JOIN @Contacts s ON t.id=s.reports_to_id WHERE s.reports_to_id=@Root _id OR @Root_id IS NULL OR t.LevelOf>1 ) SELECT * FROM StaffTree
CONCLUSION:
@Root_id=null id first_name reports_to_id Manager_id Manager_first_name LevelOf ------ ---------- ------------- ---------- ------------------ ----------- 1 Jerome NULL NULL NULL 1 2 Joe 1 1 Jerome 2 9 Bill 1 1 Jerome 2 10 Sam 9 9 Bill 3 3 Paul 2 2 Joe 3 6 David 2 2 Joe 3 7 Ian 6 6 David 4 8 Helen 6 6 David 4 4 Jack 3 3 Paul 4 5 Daniel 3 3 Paul 4 (10 row(s) affected) @Root_id='2 ' id first_name reports_to_id Manager_id Manager_first_name LevelOf ------ ---------- ------------- ---------- ------------------ ----------- 2 Joe 1 1 Jerome 1 3 Paul 2 2 Joe 2 6 David 2 2 Joe 2 7 Ian 6 6 David 3 8 Helen 6 6 David 3 4 Jack 3 3 Paul 3 5 Daniel 3 3 Paul 3 (7 row(s) affected) @Root_id='6 ' id first_name reports_to_id Manager_id Manager_first_name LevelOf ------ ---------- ------------- ---------- ------------------ ----------- 6 David 2 2 Joe 1 7 Ian 6 6 David 2 8 Helen 6 6 David 2 (3 row(s) affected)
EDIT based on the data and data specified in the OP:
try something like this:
SET NOCOUNT ON DECLARE @Company table (id int, name varchar(40) ) INSERT @Company VALUES (1,'Living Things' ) INSERT @Company VALUES (2,'Boring Company' ) DECLARE @CompanyGroup table (id int, name varchar(40), CompanyID int) INSERT @CompanyGroup VALUES (1,'Pets' ,1 ) INSERT @CompanyGroup VALUES (2,'Humans' ,1 ) INSERT @CompanyGroup VALUES (3,'Electronics' ,2 ) INSERT @CompanyGroup VALUES (4,'Food' ,2 ) DECLARE @CompanyStore table (id int, name varchar(40), CompanyGroupID int) INSERT @CompanyStore VALUES (1,'PetsStoreA' ,1 ) INSERT @CompanyStore VALUES (2,'PetsStoreB' ,1 ) INSERT @CompanyStore VALUES (3,'PetsStoreC' ,1 ) INSERT @CompanyStore VALUES (4,'PetsStoreD' ,1) INSERT @CompanyStore VALUES (5,'HumansStore' ,2 ) INSERT @CompanyStore VALUES (6,'FoodStore' ,3 ) --not provided by the OP, so I made it up DECLARE @CompanyEmployees table (id int, name varchar(10), reports_to_id int, CompanyStoreID int) INSERT @CompanyEmployees VALUES (1,'Jerome', NULL ,1) -- tree is as follows: INSERT @CompanyEmployees VALUES (2,'Joe' ,1 ,1) -- PetsStoreA PetsStoreB PetStoreC FoodStore INSERT @CompanyEmployees VALUES (3,'Paul' ,2 ,1) -- 1-Jerome 11-Alan 14-Ben 18-apple INSERT @CompanyEmployees VALUES (4,'Jack' ,3 ,1) -- / \ / \ / / \ INSERT @CompanyEmployees VALUES (5,'Daniel',3 ,1) -- 2-Joe 9-Bill 12-Ally 13-Abby 15-Bill 19-pear 20-grape INSERT @CompanyEmployees VALUES (6,'David' ,2 ,1) -- / \ \ / \ / INSERT @CompanyEmployees VALUES (7,'Ian' ,6 ,1) -- 3-Paul 6-David 10-Sam 16-Bjorn 17-Benny 21-rasin INSERT @CompanyEmployees VALUES (8,'Helen' ,6 ,1) -- / \ / \ INSERT @CompanyEmployees VALUES (9,'Bill ' ,1 ,1) -- 4-Jack 5-Daniel 7-Ian 8-Helen INSERT @CompanyEmployees VALUES (10,'Sam' ,9 ,1) -- INSERT @CompanyEmployees VALUES (11,'Alan' ,NULL ,2) --to see all trees, scroll--->> INSERT @CompanyEmployees VALUES (12,'Ally' ,11 ,2) -- INSERT @CompanyEmployees VALUES (13,'Abby' ,11 ,2) -- INSERT @CompanyEmployees VALUES (14,'Ben' ,NULL ,3) -- INSERT @CompanyEmployees VALUES (15,'Bill' ,14 ,3) -- INSERT @CompanyEmployees VALUES (16,'Bjorn',15 ,3) -- INSERT @CompanyEmployees VALUES (17,'Benny',15 ,3) -- INSERT @CompanyEmployees VALUES (18,'apple',NULL ,6) -- INSERT @CompanyEmployees VALUES (19,'pear' ,18 ,6) -- INSERT @CompanyEmployees VALUES (20,'grape',18 ,6) -- INSERT @CompanyEmployees VALUES (21,'rasin',21 ,6) -- SET NOCOUNT OFF ;WITH StaffTree AS ( SELECT c.id, c.name, c.reports_to_id, c.reports_to_id as Manager_id, cc.name AS Manager_name, 1 AS LevelOf, c.CompanyStoreID FROM @CompanyEmployees c LEFT OUTER JOIN @CompanyEmployees cc ON c.reports_to_id=cc.id WHERE c.reports_to_id IS NULL UNION ALL SELECT s.id, s.name, s.reports_to_id, t.id, t.name, t.LevelOf+1, s.CompanyStoreID FROM StaffTree t INNER JOIN @CompanyEmployees s ON t.id=s.reports_to_id ) SELECT c.id AS CompanyID, c.name AS CompanyName ,g.id AS CompanyGroupID, g.name AS CompanyName ,s.id AS CompanyStoreID, s.name AS CompanyStoreName ,t.id AS EmployeeID, t.name as EmployeeName, t.Manager_id, t.Manager_name, t.LevelOf FROM @Company c LEFT JOIN @CompanyGroup g ON c.id=g.CompanyID LEFT JOIN @CompanyStore s ON g.id=s.CompanyGroupID LEFT JOIN StaffTree t ON s.id=t.CompanyStoreID ORDER BY c.name,g.name,s.name,s.ID,t.LevelOf,t.name
CONCLUSION:
CompanyID CompanyName CompanyGroupID CompanyName CompanyStoreID CompanyStoreName EmployeeID EmployeeName Manager_id Manager_name LevelOf --------- -------------- -------------- ----------- -------------- ---------------- ----------- ------------ ----------- ------------ ------- 2 Boring Company 3 Electronics 6 FoodStore 18 apple NULL NULL 1 2 Boring Company 3 Electronics 6 FoodStore 20 grape 18 apple 2 2 Boring Company 3 Electronics 6 FoodStore 19 pear 18 apple 2 2 Boring Company 4 Food NULL NULL NULL NULL NULL NULL NULL 1 Living Things 2 Humans 5 HumansStore NULL NULL NULL NULL NULL 1 Living Things 1 Pets 1 PetsStoreA 1 Jerome NULL NULL 1 1 Living Things 1 Pets 1 PetsStoreA 9 Bill 1 Jerome 2 1 Living Things 1 Pets 1 PetsStoreA 2 Joe 1 Jerome 2 1 Living Things 1 Pets 1 PetsStoreA 6 David 2 Joe 3 1 Living Things 1 Pets 1 PetsStoreA 3 Paul 2 Joe 3 1 Living Things 1 Pets 1 PetsStoreA 10 Sam 9 Bill 3 1 Living Things 1 Pets 1 PetsStoreA 5 Daniel 3 Paul 4 1 Living Things 1 Pets 1 PetsStoreA 8 Helen 6 David 4 1 Living Things 1 Pets 1 PetsStoreA 7 Ian 6 David 4 1 Living Things 1 Pets 1 PetsStoreA 4 Jack 3 Paul 4 1 Living Things 1 Pets 2 PetsStoreB 11 Alan NULL NULL 1 1 Living Things 1 Pets 2 PetsStoreB 13 Abby 11 Alan 2 1 Living Things 1 Pets 2 PetsStoreB 12 Ally 11 Alan 2 1 Living Things 1 Pets 3 PetsStoreC 14 Ben NULL NULL 1 1 Living Things 1 Pets 3 PetsStoreC 15 Bill 14 Ben 2 1 Living Things 1 Pets 3 PetsStoreC 17 Benny 15 Bill 3 1 Living Things 1 Pets 3 PetsStoreC 16 Bjorn 15 Bill 3 1 Living Things 1 Pets 4 PetsStoreD NULL NULL NULL NULL NULL (23 row(s) affected)
EDIT after editing OP, indicating that We would like to see it in tree form .
The question is tagged with sql-server-2008 and hierarchical-data , and the OP wants to perform complex formatting to display the data. However, this type of processing and mapping is not the domain of TSQL and is a very clear example of where the application language should process and format the flat data provided by the SQL query. I have provided such a request that can be used by the application to create a visual tree. Also note that a simple tree example (no more than two children per parent) may not be very realistic, and when there are many children for one parent, the display will be difficult to create and not pleasing to the eye.