Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
Solution 4
/* Problem: Write a SQL statement to show an employee management reporting order starting with Emily Theron. Add your solution at the end. Expected Output: EmployeeId FirstName LastName 10 Emily Theron 7 Olivia Johansson 5 Jacob Foxx 2 Liam Brolin 1 Noah Clooney */ -- Create a table called Employees CREATE TABLE Employees ( EmployeeId INT PRIMARY KEY, FirstName VARCHAR(200), LastName VARCHAR(200), ManagerEmployeeId INT) GO -- Fill Employee table INSERT INTO Employees VALUES(1, 'Noah', 'Clooney', NULL) INSERT INTO Employees VALUES(2, 'Liam', 'Brolin', 1) INSERT INTO Employees VALUES(3, 'Mason', 'Farrell', 1) INSERT INTO Employees VALUES(4, 'Emma', 'Jolie', 1) INSERT INTO Employees VALUES(5, 'Jacob', 'Foxx', 2) INSERT INTO Employees VALUES(6, 'William', 'Damon', 4) INSERT INTO Employees VALUES(7, 'Olivia', 'Johansson', 5) INSERT INTO Employees VALUES(8, 'Isabella', 'Portman', 1) INSERT INTO Employees VALUES(9, 'Ethan', 'Firth', 6) INSERT INTO Employees VALUES(10, 'Emily', 'Theron', 7) GO ------------------------------------------------------------------ -- Sql Server 2014 Express Edition - 12.0.2000.8 -- Solution. ------------------------------------------------------------------ DECLARE @listOfIDs VARCHAR(1024); WITH hierarchycte(id, ManagerEmployeeId, LEVEL, treepath) AS ( SELECT EmployeeId AS id, ManagerEmployeeId, 0 AS LEVEL, CAST(EmployeeId AS VARCHAR(1024)) AS treepath FROM Employees WHERE ManagerEmployeeId IS NULL UNION ALL -- and now for the recursive part SELECT e.EmployeeId AS id, e.ManagerEmployeeId, hierarchycte.LEVEL + 1 AS LEVEL, CAST(hierarchycte.treepath + ',' + CAST(e.EmployeeId AS VARCHAR(1024)) AS VARCHAR(1024)) AS treepath FROM Employees e INNER JOIN hierarchycte ON hierarchycte.id = e.ManagerEmployeeId) SELECT @listOfIDs = treepath FROM hierarchycte where id = 10 ORDER BY treepath DECLARE @SQLQuery AS NVARCHAR(500) SET @SqlQuery = ' SELECT EmployeeId, FirstName, LastName FROM Employees WHERE EmployeeId IN (' + @listOfIDs + ') ORDER BY EmployeeId DESC' EXECUTE(@SQLQuery)
run
|
edit
|
history
|
help
0
Primeiro
food_plan
db3
Crea, confirma y despliega tablas
Employee Department Interview Questions
c12part2
20181CSE0068
"OrderForm.asp?Cart="
hhhhhhhhhhh
service_delivery_task