Appreciate your article.

This is with lot of explanation, but we can achieve the output more efficient way with the sorting of rolename also:

select distinct a.userid, substring(b.rolename, 1, len(b.rolename )-1) from @UserRole a

cross apply (select distinct RoleName + ', ' from @UserRole where a.userid = userid

for xml path('')) b (rolename)