I am trying to put the FirstName and EmailAddress in one column. For each role I want to see the Users that are assigned to that role.
I have the below code. I receive data but there are 16 names for "Received" in separate rows and I want ALL of the 16 names in 1 column.
WHEN SR.id_roles = 5 THEN
WHEN SR.id_roles = 6 THEN
'Repaired and Closed'
WHEN SR.id_roles = 12 OR SR.id_roles = 13 THEN
WHEN SR.id_roles = 14 THEN
END AS Roles,
STUFF((SELECT '; ' + BU.firstName + ' (' + BU.email_user + ')'
FROM BASE_User BU
WHERE BU.id_user = SRU.id_user
FOR XML PATH('')), 1, 1, '') AS [FoundUsers]
FROM SYS_Roles SR
INNER JOIN SYS_RolesUser SRU ON SR.id_roles = SRU.id_roles
WHERE SR.id_roles IN (5, 6, 112, 13, 14)
The STUFF Function works. I tested it alone without the GROUP BY in the STUFF Function.
I tried adding GROUP BY SR.id_roles at the end of the SQL and I receive an error -> Column 'SYS_RolesUser.id_user' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.