SQL Server parent child (parent see all everything)?

Question

This is my table:

EmployeeID  Employee    ManagerID
---------------------------------
    1       Anna           5
    2       John           4
    3       Steve          4
    4       Lisa           1
    5       Adam          NULL
    6       Per            1

There is no problem for me to get parent and child relationship with a self-join like this:

SELECT 
    E.EmployeeID,
    E.Employee AS Employee,
    E.ManagerID,
    M.Employee AS Manager
FROM 
    Employee AS E
LEFT JOIN 
    Employee AS M ON E.ManagerID = M.EmployeeID

EmployeeID  Employee    ManagerID   Manager
1   Anna    5   Adam
2   John    4   Lisa
3   Steve   4   Lisa
4   Lisa    1   Anna
5   Adam    NULL NULL
6   Per     1    Anna

However, How would i go about to make sure that the parent see the whole hierarchy level?

I would like the table to look like this:

EmployeeID  Manager Employee    EmployeeID  
5   Adam    Anna    1
5   Adam    Per     6
5   Adam    Lisa    4
5   Adam    John    2
5   Adam    Steve   3
1   Anna    Per     6
1   Anna    Lisa    4
1   Anna    John    2
1   Anna    Steve   3
4   Lisa    John    2
4   Lisa    Steve   3

Note: in this example i only have 3 levels of manger but there can be many more

Answer 1

You can try this:

DECLARE @DataSource TABLE
(
    [EmployeeID] TINYINT
   ,[Employee] VARCHAR(12)
   ,[ManagerID] TINYINT 
);

INSERT INTO @DataSource ([EmployeeID], [Employee], [ManagerID])
VALUES (1, 'Anna', 5)
      ,(2, 'John', 4)
      ,(3, 'Steve', 4)
      ,(4, 'Lisa', 1)
      ,(5, 'Adam', NULL)
      ,(6, 'Per',  1);

WITH DataSource AS 
(
    SELECT DISTINCT DS1.*
                   ,0 AS [Level]
                   ,DS1.[EmployeeID] AS Parent
    FROM @DataSource DS1
    INNER JOIN @DataSource DS2
        ON DS1.[EmployeeID] = DS2.[ManagerID]
    UNION ALL
    SELECT DS2.*
          ,DS1.[Level] + 1
          ,DS1.Parent
    FROM DataSource DS1
    INNER JOIN @DataSource DS2
        ON DS1.[EmployeeID] = DS2.[ManagerID]
)
SELECT DS1.[EmployeeID] 
      ,DS1.[Employee] AS [Manager]
      ,DS.[EmployeeID]
      ,DS.[Employee]
FROM DataSource DS
INNER JOIN @DataSource DS1
    ON DS.[Parent] = DS1.[EmployeeID]
WHERE DS.[Level] <> 0
ORDER BY DS.[Parent] DESC;

We are using recursive CTE and it may look a kind of messy and complicated if you are seeing this syntax for the first time, but it's nothing special.

When are using recursive CTE run some performance tests in order to be sure it is the right technique for solving your issue.

Answer 2

You should use recursive CTE syntax. In the first iteration (before UNION ALL) you get all Parent-Child pairs. In the recursive part (after UNION ALL) you get the next level child for each pair and substitute it into the pair Parent-Child instead of the Child leaving Parent the same.

WITH CTE AS 
(
   SELECT TP.EmployeeID as ManagerId, 
          TP.Employee as Manager,
          TC.EmployeeID as EmployeeID, 
          TC.Employee as Employee

          FROM TEmployee as TP
          JOIN TEmployee as TC on (TP.EmployeeID = TC.ManagerID)

          UNION ALL

   SELECT TP.ManagerId as ManagerId, 
          TP.Manager as Manager,
          TC.EmployeeID as EmployeeID, 
          TC.Employee as Employee

          FROM CTE as TP
          JOIN TEmployee as TC on (TP.EmployeeID = TC.ManagerID)
)
SELECT * FROM CTE Order By ManagerID

result:

+-----------+---------+------------+----------+
| ManagerId | Manager | EmployeeID | Employee |
+-----------+---------+------------+----------+
|         1 | Anna    |          4 | Lisa     |
|         1 | Anna    |          6 | Per      |
|         1 | Anna    |          2 | John     |
|         1 | Anna    |          3 | Steve    |
|         4 | Lisa    |          2 | John     |
|         4 | Lisa    |          3 | Steve    |
|         5 | Adam    |          1 | Anna     |
|         5 | Adam    |          4 | Lisa     |
|         5 | Adam    |          6 | Per      |
|         5 | Adam    |          2 | John     |
|         5 | Adam    |          3 | Steve    |
+-----------+---------+------------+----------+