The first part of my problem is not knowing how to correctly ask and phrase this question, so I apologize if this is a duplicate.

Aside from a users table, I have a table like this

```
[UserRoles]
UserID | RoleName
-------|---------
1      | Role1
1      | Role2
1      | Role3
1      | Role4
2      | Role1
2      | Role4

```

Another table like this:

```
[RoleRoleTypes]
RoleName | Function
-------------------
Role1    | FunctionA
Role2    | FunctionA
Role3    | FunctionB
Role4    | FunctionC

```

What I want to end up with is a table with the `UserID`, a comma separated list of roles, a comma separated list of functions.

End Result

```
UserId | Roles                       | Functions
-------|---------------------------- |------------------------------------
1      | Role1, Role2, Role3, Role4  | FunctionA, FunctionB, FunctionC 
2      | Role1, Role4                | FunctionC

```

The first two columns are rather easy, using stuff to create the comma seperated list of roles. But I am unsure how to go about calculating a list of functions given that multiple roles make up a function. A user must have all the roles listed to have the function. So if you have role1, but not role 2, you don't have FunctionA. I tried a variety of joins but never got anywhere close to the correct data. Any advice? 

Here is what I have for the first two columns

```
SELECT u.email, Roles = STUFF(
    (SELECT ',' + RoleName
    From UserRoles ur
    WHERE ur.UserID = u.UserID
    FOR XML PATH('')), 1, 1, '')
FROM Users u

```

*EDIT* Added clarification and end result example