vendredi 31 juillet 2015

Recursive CTE - Employee with multiple Manager

I have a table like follow

+-----------+------------+
| ManagerID | EmployeeID |
+-----------+------------+
| MAN001    | BOY001     |
| MAN001    | BOY002     |
| MAN001    | BOY003     |
| MAN001    | BOY004     |
| MAN001    | BOY005     |
| BOY005    | KID001     |
| BOY005    | KID002     |
| BOY005    | KID003     |
| BOY005    | KID004     |
| MAN002    | BOY005     |
| MAN002    | BOY004     |
| BOY004    | KID001     |
| BOY004    | KID002     |
| BOY004    | KID003     |
| BOY004    | KID004     |
| KID002    | SOM001     |
| KID002    | SOM002     |
| KID002    | SOM003     |
+-----------+------------+

The main point is that MAN001 does not exists in EmployeeID, meaning MAN001 is top level. But BOY005 can work for MAN001 or MAN002 and more ... The same with KID00X CAN work with Any manager MAN00X or BOY00X.

It is also possible that KID001 KID002 KID003 KID004 may have an employee, eg KID002 has three Employee SOM001 SOM002 and SOM003 BUT BOY005 will never be an employee of KID because we know that BOY005 is the manager for KID.

The output that I would like to get is as follow, where the column E can grow as much as the data has.

+--------+--------+--------+--------+-------+
|   M1   |   E1   |   E2   |   E3   |  ...  |
+--------+--------+--------+--------+-------+
| MAN001 | BOY001 |        |        |       |
| MAN001 | BOY002 |        |        |       |
| MAN001 | BOY003 |        |        |       |
| MAN001 | BOY004 |        |        |       |
| MAN001 | BOY005 | KID001 |        |       |
| MAN001 | BOY005 | KID002 |        |       |
| MAN001 | BOY005 | KID003 |        |       |
| MAN001 | BOY005 | KID004 |        |       |
| MAN001 | BOY005 | KID002 | SOM001 |       |
| MAN001 | BOY005 | KID002 | SOM002 |       |
| MAN001 | BOY005 | KID002 | SOM003 |       |
+--------+--------+--------+--------+-------+

I tried to follow this basic simple recursive cte. But the example does not consider if an employee exists in multiple manager. http://ift.tt/QOJbrI

Also another different case with the blog post above is in my case Top level ManagerID simply don't exists in EmployeeID

WITH Emp_CTE AS (
SELECT EmployeeID, ManagerID
FROM Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT e.EmployeeID, , e.ManagerID
FROM HumanResources.Employee e
INNER JOIN Emp_CTE ecte ON ecte.EmployeeID = e.ManagerID
)
SELECT *
FROM Emp_CTE

Thank you

Aucun commentaire:

Enregistrer un commentaire