Run Code
|
API
|
Code Wall
|
Users
|
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
Please
log in
to post a comment.
Max from multiple columns
Table
SQL Test
StackOverflow_53799678
Joins outside XML element
DETAILS
Library Datebase
Función Borrar carácter inicial repetido
2021.02.24 Lab 4
Combined Where and having
Please log in to post a comment.