CFGRID :: How do I query a table but have one column display as a list from another table

I have a CFGRID in which I am trying to do the following and do not know if this can be accomplished with SQL only or if there is a way that I could do it in my coldfusion cfc.

This is what the return statement looks like at the moment:

<cfreturn QueryConvertForGrid(getEmps, page, pageSizeToUse)>

Select allOpen in new window

- If done with in coldfusion how do I return a modified data set to the cfgrid?

- else how do i accomplish the following in SQL only?

I want to select * from a table of employees and then add on a field called Roles which is stored in a seperate table and has a 1-N relationship.  The Roles table only contains IDs for the employee and role. The UserRoles table contains the Role name.

Table 1= Employee Info
Table 2= Roles - Contains role names
Table 3= UserRoles - Contains the roles a user has

1 Employee has many roles

They are connected by:

Employees.EmpID=Roles.EmpRoleID
Roles.Role_ID=UserRoles.rID

I want the output to be one line per employee with roles displaying as a comma delimited list.

Example:

EmpName | Phone             | Email           | Roles
--------------------------------------------------------------------------------------
John Doe   | 555-555-5555 | xx@xx.com | Role1, Role2, Role3, ...



This is as far as I've gotten ( it gets the roles for one emp if I enter an ID for EmpID ):
It doesnt include the other info i need from the employees table.

DECLARE @listStr varchar(500)
SELECT @listStr = LTRIM(RTRIM(COALESCE(@listStr+', ', '') + [Role]))
FROM roles, userRoles
WHERE Role_ID=rID
	AND EmpID=...?...
SELECT @listStr as List

Select allOpen in new window