I assumed some of the structures were not yours but the post that won't execute certainly was. At any rate I think that something like this will get you what you are looking for.

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 employee e

join spaces s on e.space_Number = s.space_id

)

select CC, Space_Number, SpaceType, Space_SpaceNumber,

MAX(case when RowNum = 1 then FirstName end) as FirstName1,

MAX(case when RowNum = 1 then LastName end) as LastName1,

MAX(case when RowNum = 2 then FirstName end) as FirstName2,

MAX(case when RowNum = 2 then LastName end) as LastName2

from SortedData s

group by

CC, Space_Number, SpaceType, Space_SpaceNumber

This is using a cte (common table expression). You should probably read up on them.

Chris is using an alias. I did the same thing in the code I just posted. You might want to read up on aliases in sql server. They will make your life a LOT easier.