Before we even get into a query your code has some pretty serious issues. I know you are new and I am not trying to bust your chops but you have some major datatypes mismatches and naming challenges. Let's take a look at the column CC. I am guessing that abbreviation means something? In general it is best practice to name your columns with something that gives an indication of what it is. Then you have a datatype of float but all the values you posted are ints.

Space_Number. The datatype is int in the employee table. I think this is the column used to join to space? However in the space table that name has changed to space_id. Then there is a column in the space table called space_number, but it is not a number because there are characters in the column. You should find a column name and keep the same across all tables. Otherwise you are constantly going to be fighting figuring out what column is what.

Reserved words and spaces. It is generally accepted best practice to avoid using sql server reserved words as column names. Also, using spaces in column is generally not done (although it is allowed).

You might also want to read up on normalization. Things like SpaceType are ok but in this case it is denormalized.

OK back to the task for which you have actually come seeking help and guidance on...

You say you want First1, First 2, Last1, Last2 etc. Not a problem to have NULL when there isn't a second. But what about if there is a third? You just skip that one? How are we going to define first, second, etc...what is the ordering condition?

Thanks for posting the ddl and sample data. There were a lot of issues with what you put together so I am assuming you didn't test it because it wouldn't execute as posted. I cleaned this up so we have something work with.

IF OBJECT_ID('Employee','U') IS NOT NULL

DROP TABLE dbo.Employee

--===== Create the test table with

CREATE TABLE dbo.Employee

(

Employee_ID INT PRIMARY KEY,

CC FLOAT,

FirstName NVARCHAR(50),

LastName NVARCHAR(50),

Space_Number INT,

[Employee Type] NVARCHAR(50)

)

--===== Insert the test data into the test table

INSERT INTO dbo.Employee

(Employee_ID, CC, FirstName, LastName, Space_Number, [Employee Type])

SELECT 20, 9700, 'Joseph', 'McSmith', 457, '(F.T)' UNION ALL

SELECT 21, 4800, 'Pat', 'Dunlovey', 1456, '(F.T)' UNION ALL

SELECT 24, 9150, 'Scott', 'Chatting', 638, '(F.T)' UNION ALL

SELECT 25, 9150, 'Brian', 'Hutchin', 1270, '(F.T)' UNION ALL

SELECT 779, 9150,'Peter', 'Galant', 1270, '(F.T)' UNION ALL

SELECT 26, 8400, 'Bob', 'Pryt', 629, '(F.T)' UNION ALL

SELECT 27, 7570, 'Karen', 'Home', 620, '(F.T)' UNION ALL

SELECT 28, 7400, 'Helen', 'Mill', 609, '(F.T)' UNION ALL

SELECT 30, 7400, 'Kimberly', 'Parth', 608, '(F.T)'

--===== If the test table already exists, drop it

IF OBJECT_ID('Spaces','U') IS NOT NULL

DROP TABLE dbo.Spaces

--===== Create the test table with

CREATE TABLE dbo.Spaces

(

Space_ID INT PRIMARY KEY,

BuildingNum NVARCHAR(50),

FloorNum NVARCHAR(50),

Space_Number NVARCHAR(255),

SpaceType NVARCHAR(50),

Area INT

)

--===== Insert the test data into the test table

INSERT INTO dbo.Spaces

(Space_ID, BuildingNum, FloorNum, Space_Number, SpaceType, Area)

SELECT '457','84','4S','84R4141','Office','194' UNION ALL

SELECT '608','84','2S','84R2121','Office','130' UNION ALL

SELECT '609','84','2S','84R2120','Office','126' UNION ALL

SELECT '620','84','1N','84R1576','Storage','1002' UNION ALL

SELECT '629','84','1N','84R1558','Office','109' UNION ALL

SELECT '638','84','1N','84R1544','Workspace','69' UNION ALL

SELECT '1270','158','1','158R1533','Office','139' UNION ALL

SELECT '1456','158','3','158R3111','Office','208'

Now if I understand the result set you are looking it would start with something like this query?

select *

from employee e

join spaces s on e.space_Number = s.space_id