I like this article, it was a problem I had to solve previously.

I particularly like the way you solved the problem of the final ',' at the end of the string. Very neat.

I have to say I was totally lost by the end of your explanation and found the code too difficult to follow.

The following is an adaptation of your 2nd stage code, but using Group By RoleName which then allows Order By MIN(RoleAssignedDate) and thereby giving the order required.

SELECT DISTINCT u1.UserID,

REPLACE(

(SELECT u2.RoleName + ',' AS 'data()'

FROM @UserRole u2

WHERE u2.UserID = u1.UserID

GROUP BY RoleName

ORDER BY MIN(RoleAssignedDate)

FOR XML PATH('')

) + '$', ',$', ''

) AS Roles

FROM @UserRole u1

Cheers,

Nick