Using SQL STUFF to concatenate rows that I cannot group by

Question

I am trying to get a comma-delimited list of user roles for a specific app. I've used STUFF and FOR XML PATH('') for this problem before, but I don't know if I can in this situation. Here is the query that shows what I want to do, but doesn't work:

SELECT FName, LName, Email, Username, 
    STUFF((SELECT r.RoleDescription FROM Roles r2 WHERE r2.RoleID = r.RoleID FOR XML PATH('')),1,1,'') as RoleList
FROM Users u
INNER JOIN UserRole ur on u.UserID = ur.UserID
INNER JOIN Roles r on r.RoleID = ur.RoleID
WHERE ur.AppID = 506
GROUP BY FName, LName, Email, Username

This doesn't work because my where clause for the subquery contains a column that isn't in the GROUP BY. However if I group by the RoleID I lose the ability to combine those rows in the aggregate function.

Is there another way I can do this? Or a way that I could fix my query to achieve the result?

Answer 1

OUTER APPLY is far neater

SELECT FName, LName, Email, Username, 
    STUFF(x.csv,1,1,'') as RoleList
FROM Users u
INNER JOIN UserRole ur on u.UserID = ur.UserID
OUTER APPLY
    (
    SELECT
        ',' + r.RoleDescription
    FROM
        Roles r
    WHERE
        r.RoleID = ur.RoleID
    FOR XML PATH ('')
    ) x (csv)
WHERE ur.AppID = 506
GROUP BY FName, LName, Email, Username, x.csv

However, you may not need group by now because you don't repeat per joined Role row

SELECT FName, LName, Email, Username, 
    STUFF(x.csv,1,1,'') as RoleList
FROM Users u
INNER JOIN UserRole ur on u.UserID = ur.UserID
OUTER APPLY
    (
    SELECT
        ',' + r.RoleDescription
    FROM
        Roles r
    WHERE
        r.RoleID = ur.RoleID
    FOR XML PATH ('')
    ) x (csv)
WHERE ur.AppID = 506

For completeness, DISTINCT works better then GROUP BY for in-line sub queries because it is applied much later to the actual values. RoleId is not part of the actual values so won't break DISTINCT

Update for the filter which is actually on UserRole, which means we can use CROSS APPLY

SELECT FName, LName, Email, Username, 
    STUFF(x.csv,1,1,'') as RoleList
FROM Users u
CROSS APPLY
    (
    SELECT
        ',' + r.RoleDescription
    FROM
        UserRole ur
        JOIN
        Roles r ON ur.RoleID = r.RoleID
    WHERE
        u.UserID = ur.UserID
    FOR XML PATH ('')
    ) x (csv)
WHERE u.UserID IN (Select UserID FROM UserRole WHERE AppID = 506)