Query to comine data into a signle column

[gavinjb]

Hi,

I am trying to write a query that can combine multiple rows of data into a single column. What I want is a result set which has JobRef as coulmn 1 and column 2 will be all users assigned to the job e.g. "User 1, User2, User 3" (from User.UserName)

The structure of the database is below, can anyone think of an easy to to resolve this.

Job:
JobRef
Job Title

Assignment:
JobRef
UserID

User:
UserID
UserName
DeptID

Thanks,


Gavin

 

[KjWhal]

Hi,

I am trying to write a query that can combine multiple rows of data into a single column. What I want is a result set which has JobRef as coulmn 1 and column 2 will be all users assigned to the job e.g. "User 1, User2, User 3" (from User.UserName)

The structure of the database is below, can anyone think of an easy to to resolve this.

Job:
JobRef
Job Title

Assignment:
JobRef
UserID

User:
UserID
UserName
DeptID

Thanks,


Gavin

I think what you're looking for is

SELECT Job.JobRef, User.UserName 
FROM Job, User, Assignment
WHERE Assignment.JobRef = Job.JobRef AND Assignment.UserID = User.UserID
ORDER BY JobRef