主表连接多行从表数据转成一行并用逗号连接

SELECT Id,UserName,[Password],
STUFF(( SELECT ‘,‘ + [Description]
FROM (SELECT uir.UserId,rol.[Description] FROM [Security].UserInRole uir INNER JOIN [Security].[Role] rol ON rol.Id=uir.RoleId) as userRoleView
WHERE userRoleView.UserId = us.Id
FOR
XML PATH(‘‘)
), 1, 1, ‘‘) AS RoleDescriptions
FROM [Security].[User] us