WITH CTE

AS

(

SELECT ROW_NUMBER() OVER

(

PARTITION BY u2.UserID

ORDER BY MIN(u2.RoleAssignedDate)

) AS RowNum,

u2.UserID,

u2.RoleName

FROM @UserRole u2

GROUP BY u2.UserID,

u2.RoleName

)

SELECT DISTINCT u1.UserID,

SUBSTRING

(

(

SELECT ', ' + c.RoleName AS '*'

FROM CTE c

WHERE u1.UserID = c.UserID

ORDER BY c.RowNum

FOR XML PATH('')

), 3, 8000

) AS Roles

FROM @UserRole u1;

Should give the same result without the XQuery.