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?

 

[Blackstar_solar]

SQL is designed to do set operations, what you want is not a set operation, as such you will need to use a cursor.

Cursors are bad..mmmkay?

If you're using .net look into LINQ (only avaliable in .net 3.5)

edit, or do it in code.

I.e. return a DataTable or similar in the form

user1 role1
user1 role2
user2 role1
user3 role2
user3 role3

etc

then loop through and build up your data how you want.

 

[Padders]

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

 

[Blackstar_solar]

That'll only work in SQL 2005 I think, could be wrong though

 

[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