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
BRYAN
Finding Duplicate Rows in sql
1
Admno
FIGURA5.1
Pivot unpivot
how-to-assign-a-random-value-in-a-select-statement-in-sqlserver
quan ly chuyen bay
mySQL_Glx_12dec
Loan table