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
I realize this, I was not the one who created the database. Also, for other reasons not mentioned, they want the names of the fields to remain unchanged. I apologize for any frustration it may cause you, but that is a problem with management that I would have to tackle on another day.
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?
If you see ChrisM's post, that is the format of the result I need. I would actually prefer to have a null if there isn't a second employee. A third employee will be ignored completely.
As for defining first and second. If you were to order by the Space_Number or Space_ID (I understand this is confusing, but each space_number also has the same space_id, so ordering by either would work), the first employee to show up would be first, and the second would be second. Or they could be chosen by their employee_id.
Essentially, it does not matter which employee is the first or second, as long as they each appear in the same row (in the same space_number) with the correct first and last names. Neither employee is given precedent over another and their selection as first and second is arbitrary.
ChrisM has the correct idea here:
ChrisM@home (8/12/2013)
Could it be as simple as this?
SELECTs.Space_Number,
MIN(e.First) AS First1,
MIN(e.Last) AS Last1,
MAX(e.First) AS First2,
MAX(e.Last) AS Last2,
MIN(e.CC) AS FirstOfCC,
MIN(e.[Employee Type]) AS [FirstOfEmployee Type],
s.[Space Type]
FROM Spaces s
LEFT JOIN Employee e
ON s.Space_ID = e.Space_Number
GROUP BY s.Space_Number, s.[Space Type];
This is exactly the format that I need my database to be in. However, simply using MIN and MAX could separate the first and last names of different employees. For example, when I ran this code, for Space_Number 158R1533, it went across like: Brian, Galante, Peter, Hutchinson ... when we would want either Brian, Hutchinson, Peter, Galante or Peter, Galante, Brian, Hutchinson.
However you did interpret my problem correctly and I thank you for the effort! I almost thought it was actually that easy until I looked through the results =(
Also, how if you care to explain, how can you just write s.Space_Number or e.First without using the whole "prefix" if you will. Like I said... I am terribly new to SQL.