September 25, 2013 at 10:24 pm
Comments posted to this topic are about the item Recursive CTE Example
September 26, 2013 at 9:29 am
Neil,
Awesome code - thanks! Still trying to get my head around it - lol.
A suggestion: I was looking for a way to get the manager's name as well as their ID in the results and came up with the following:
1) Set the big boss' Reports_To_ID = to his Employee_ID, instead of null; i.e. change:
INSERT INTO @Employees
SELECT 1,'Roy Hodgson',NULL,'Managing Director'
to
INSERT INTO @Employees
SELECT 1,'Roy Hodgson',1,'Managing Director'
2) Change the final select statement to:
SELECT a.Employee_ID ,
a.Employee_Name ,
a.Reports_To_ID ,
b.Employee_Name Manager_Name,
a.Job_Title
FROM _hirearchy a JOIN _hirearchy b ON b.Employee_ID = a.Reports_To_ID
I've attached my code for your / everyone's benefit to see the changes.
The only problem I see with this is if you change the @BossID to a different value (7248 for Kyle Walker, for example), he doesn't show up in the results as an Employee. Can you think of a way to change your query to return the Manager_Name? That would be awesome!
Kudos again,
Mike
September 28, 2013 at 10:21 pm
Crisp. Well written
October 10, 2013 at 12:20 pm
Hello everyone!
Great post!
The only way I've found to get the manager name is changing the table "_hirearchy b" for @Employees in the final select with a left join. It also works with the original insert for the ID 1 with Reports_To_ID = NULL.
I guess this decrease performance or, at least, you lose some of the advantages of using CTE.
FROM _hirearchy a LEFT OUTER JOIN @Employees b ON b.Employee_ID = a.Reports_To_ID
Please, tell me if this is too awful :-P.
Greetings to all!
October 10, 2013 at 1:07 pm
Awesome thanks Mike!!
April 27, 2016 at 4:56 pm
Thanks for the script.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply