Let's skip option B as option A is not only feasible it would be the preferred method. All you need to do is wrap Name1 with an ISNULL check. The problem is that the query selects from employee first so there is no way you will get a space that doesn't already have an employee. You could switch the order of the tables and change the join to employees from an inner join to a left join. This would then return all spaces even if there is no match on employee. In the sample data you provided there are no spaces without employees.

Let's start by adding a new space with no employees.

insert Spaces

select 1234, 42, '8', 'Sean''s Space', 'Home', 654

Now we need to tweak up our code a bit so it will do what you are asking.

with SortedData as

(

select e.*, s.SpaceType, s.Space_Number as Space_SpaceNumber, ROW_NUMBER() over (partition by s.Space_Number order by (select null)) as RowNum

from spaces s

left join employee e on e.space_Number = s.space_id

)

select CC, Space_SpaceNumber, SpaceType, Space_SpaceNumber,

MAX(case when RowNum = 1 then isnull(FirstName + ' ' + LastName, 'Vacant Cubicle') end) as Name1,

MAX(case when RowNum = 2 then FirstName + ' ' + LastName end) as Name2

from SortedData s

group by

CC, Space_SpaceNumber, SpaceType, Space_SpaceNumber