Run Code
|
API
|
Code Wall
|
Users
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Blog
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.
test
20181CSE0041
QLCB_CSDL
TestTaskSolutionByTania
newtable
QUAN LY CHUYEN BAY
replace string
tr3
9.12.2020
db_hotel29102020
stackse - search stackoverflow differently
Please log in to post a comment.