Cacher is the code snippet organizer for pro developers

DECLARE @centerId INT = 1


;WITH CTE AS (
SELECT u.UserId
	,u.UserName	
	,fm.NAME AS FacilityName
FROM dbo.[User] u
INNER JOIN dbo.UserXCenter uc ON u.UserId = uc.UserId
	AND uc.IsActive = 1
	AND uc.CenterId = @centerId
INNER JOIN dbo.UserXCenterXRole ucr ON uc.UserXCenterId = ucr.UserXCenterId
	AND ucr.IsActive = 1
	AND ucr.RoleId = 2 --CenterAdmin
LEFT JOIN dbo.UserCenterRoleXFacility ucrx ON ucr.UserXCenterXRoleId = ucrx.UserXCenterXRoleId
	AND ucrx.IsActive = 1
LEFT JOIN dbo.FacilityMaster fm ON ucrx.FacilityId = fm.Id
	AND fm.IsActive = 1
WHERE u.IsActive = 1)



SELECT a.UserId,a.UserName,
Facilities = 
    STUFF((SELECT ', ' + FacilityName
           FROM CTE b 
           WHERE b.UserId = a.UserId 
          FOR XML PATH('')), 1, 2, '')
 FROM CTE a GROUP BY a.UserId,a.UserName