OK, then use GROUP:

--An alternative without ROW_NUMBER()

--How about this?

select

u.UserID

, stuff((

select ', ' + u2.RoleName

from @UserRole u2

where u2.UserID = u.UserID

and u2.RoleAssignedDate = (

select min(u3.RoleAssignedDate)

from @UserRole u3

where u3.UserID = u2.UserID

and u3.RoleName = u2.RoleName

)

order by u2.RoleAssignedDate asc

for xml path('')

), 1, 2, '') 'RoleName'

from @UserRole u

group by u.UserID

*** R.P.Rozema *****************************************************************

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

Table '#4F1BAE10'. Scan count 8, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 4 ms.

*** Brian Barkauskas ***********************************************************

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

Table '#4F1BAE10'. Scan count 8, logical reads 8, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 2 ms.