Brian Barkauskas (6/29/2010)
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.
You good folks simply aren't using enough data to claim victory in any of the cases.
--Jeff Moden
Change is inevitable... Change for the better is not.