SQL help! Joining multiple rows

[ultim]

I have three tables
users
usersrole
roles

A user can have one to many roles (reason for usersrole table)

I want to write an sql which will return a row for each user and all the roles it belongs to in a comma separated format.
i.e.

USER       USERROLE
Bob        Member, Admin
Dave       Member

How do i do that?

 

[ultim]

a UDF, something along the lines of:


DECLARE @roles VARCHAR(128)

SELECT
@roles = COALESCE(@roles + ', ', '') + userrole.role
FROM
userrole
WHERE
userrole.user = 'bob'

RETURN @roles

I dont see how it actually works but ill give it ago.
thanks for the help