Un moyen de combiner les lignes set?

Je souhaite combiner des lignes afin que ma requête de résultat soit identique à la requête de résultat correcte.

Mon code:

SELECT space_num, name AS employee, NULL AS employee2 FROM (dbo.employee INNER JOIN dbo.occupancy ON employee.id = occupancy.employee_id) RIGHT JOIN dbo.space ON occupancy.space_id = space.id UNION SELECT space_num, NULL AS employee, name AS employee2 FROM (dbo.employee INNER JOIN dbo.allocation ON employee.id = allocation.employee_id) RIGHT JOIN dbo.space ON allocation.space_id = space.id

Mon résultat:

ma requête de résultat

Résultat attendu:

Requête de résultat correcte

Le plus probable

 SELECT e.name as employee, occupiedSpace, allocatedSpace FROM dbo.employee e LEFT JOIN ( SELECT COALESCE(oNm.employee_id, aNm.employee_id) as employee_id , aNm.space_num as occupiedSpace , oNm.space_num as allocatedSpace FROM (SELECT employee_id, rn = row_number() over (partition by employee_id order by id), s.space_num FROM dbo.occupancy sp JOIN dbo.space s ON sp.space_id = s.id ) oNm FULL JOIN (SELECT employee_id, rn = row_number() over (partition by employee_id order by id), s.space_num FROM dbo.allocation sp JOIN dbo.space s ON sp.space_id = s.id ) aNm ON oNm.employee_id = aNm.employee_id and oNm.rn= aNm.rn ) t ON e.employee_id = t.employee_id