SQL Employee Org Hierarchy

Question

I have an employee table.

Employee	Supervisor	YearMonth
a	b	202201
b	c	202201
c	null	202201
e	b	202202
b	d	202202

And I want to create an org hierarchy that is at a column level in SQL expected output =

Employee	Supervisor	YearMonth	Org1	Org2
a	b	202201	b	c
b	c	202201	c	null
c	null	202201	null	null
e	b	202202	b	d
b	d	202202	d	null

I have been able to achieve this in python, but want the same output in SQL.

Answer 1

I've tried to get what you want but maybe you have provided dud data!?

as Employee b is listed twice so he has supervisor c and d, hence why the example below differ from what you want.

I've provided 3 types or approaches, run them all and see what you think, I think the third one is closest to what you want

IF EXISTS (SELECT  * FROM tempdb.dbo.sysobjects O WHERE O.xtype in ('U') AND O.id = object_id(N'tempdb..#Employee'))
BEGIN
    PRINT 'Removing temp table #Employee'
    DROP TABLE #Employee;
END

CREATE TABLE #Employee (
    EmployeeId VARCHAR(1), 
    SupervisorId VARCHAR(1), 
    YearMonth VARCHAR(6)
)

INSERT INTO #Employee (EmployeeId, SupervisorId, YearMonth)
VALUES('a', 'b', '202201'),
('b', 'c', '202201'),
('c', NULL, '202201'),
('e', 'b', '202202'),
('b', 'd', '202202')

-- SELECT * FROM #Employee

/*
Approach 1: Use a CTE to recursively get the hierarchy
*/
;WITH MyTable AS 
(
    SELECT EmployeeId, SupervisorId, 1 AS [Level]
    FROM #Employee
    WHERE EmployeeId = 'a' -- Remove this to get everyone and the levels of hierarchy

    UNION ALL

    SELECT e.EmployeeId, e.SupervisorId, T.[Level] + 1
    FROM #Employee AS E
    INNER JOIN MyTable AS T ON T.SupervisorId = E.EmployeeId
)

SELECT * FROM MyTable
ORDER BY [Level] ASC


/*
Approach 2: 
What you are asking is just display the manager for employee and supervisor
But this has more data as supervisor b has more than one supervisor
*/

SELECT 
E.EmployeeId AS Employee, 
E.SupervisorId AS Supervisor, 
E.YearMonth, 
E.SupervisorId AS Org1, 
E2.SupervisorId AS Org2
FROM #Employee AS E
LEFT JOIN #Employee AS E2 ON E2.EmployeeId = E.SupervisorId


GO

/*
Approach 3: 
Using a outer apply will just get the data for each row
*/

SELECT 
E.EmployeeId AS Employee, 
E.SupervisorId AS Supervisor, 
E.YearMonth, 
E.SupervisorId AS Org1, 
T.SupervisorId AS Org2
FROM #Employee AS E
OUTER APPLY (
    SELECT TOP 1 *
    FROM #Employee AS E2
    WHERE E2.EmployeeId = E.SupervisorId
) AS T

Output to show, note the first one I just focus on Employee a but you can remove that to get everyone's hierarchy and display the level they are in.