jamesheslin123 (8/12/2013)

---

Hello,

I need to convert a query in MS Access to a view in SQL Server 2005. The view will draw from two tables: Employee and Spaces. I want the view to be grouped by the space_number. Also, if there are two employees for one space_number, I need the two employees' names to be listed in the same row of the view.

Here is the Access code of the query, which uses the FIRST function to do what I need:

SELECT Spaces.Space_Number, First(Employee.First) AS First1, First(Employee.Last) AS Last1, Last(Employee.First) AS First2, Last(Employee.Last) AS Last2, First(Employee.CC) AS FirstOfCC, First(Employee.[Employee Type]) AS [FirstOfEmployee Type], Spaces.[Space Type]

FROM Spaces LEFT JOIN Employee ON Spaces.Space_ID = Employee.Space_Number

GROUP BY Spaces.Space_Number, Spaces.[Space Type];

Below is the SQL Code that I have so far... :

SELECT TOP (100) PERCENT dbo.Spaces.Space_Number, dbo.Spaces.Space_ID, dbo.Employee.First AS First1, dbo.Employee.Last AS Last1, dbo.Employee.First AS First2,

dbo.Employee.Last AS Last2, dbo.Employee.CC, dbo.Employee.[Employee Type], dbo.Spaces.[Space Type]

FROM dbo.Employee RIGHT OUTER JOIN

dbo.Spaces ON dbo.Employee.Space_Number = dbo.Spaces.Space_ID

ORDER BY dbo.Spaces.Space_Number

Also, if there are two employees for one space_number, I need the two employees' names to be listed in the same row of the view.