Sample data set:

select 'Report1' as reportID,'Browser' as RoleID, 'YChen' as User2

into #tbl

UNION

select 'Report1' as reportID,'Browser' as RoleID,'AChen' as User2

UNION

select 'Report1' as reportID,'Browser' as RoleID,'BChen' as User2

UNION

select 'Report2' as reportID,'Browser' as RoleID,'YChen' as User2

UNION

select 'Report2' as reportID,'Browser' as RoleID,'XChen' as User2

UNION

select 'Report2' as reportID,'Browser' as RoleID,'BChen' as User2

UNION

select 'Report1' as reportID,'Writer' as RoleID,'YChen' as User2

UNION

select 'Report1' as reportID,'Writer' as RoleID,'XChen' as User2

reportIDRoleIDUser2

Report1BrowserAChen

Report1BrowserBChen

Report1BrowserYChen

Report1WriterXChen

Report1WriterYChen

Report2BrowserBChen

Report2BrowserXChen

Report2BrowserYChen

~~~

Desired output:

reportIDroleIDlist

Report1BrowserAChen, BChen, YChen

Report1WriterXChen, YChen

Report2BrowserBChen, XChen, YChen

~~~

CTE method used (takes a long time with many records):

WITH CTE ( reportID, roleID, list, user2, length )

AS ( SELECT reportID, roleID, CAST( '' AS VARCHAR(8000) ), CAST( '' AS VARCHAR(8000) ), 0

FROM #tbl

GROUP BY reportID, roleID

UNION ALL

SELECT p.reportID, p.roleID, CAST( list +

CASE WHEN length = 0 THEN '' ELSE ', ' END + RTRIM(p.[user2]) AS VARCHAR(8000) ),

CAST(p.[user2] AS VARCHAR(8000)), length + 1

FROM CTE c

INNER JOIN #tbl p

ON c.reportID = p.reportID

and c.roleID = p.roleID

WHERE p.user2 > c.user2 )

SELECT reportID, roleID, list

FROM ( SELECT reportID, roleID, list,

RANK() OVER ( PARTITION BY reportID, roleID ORDER BY length DESC )

FROM CTE ) D ( reportID, roleID, list , rank )

WHERE rank = 1

~~~

Thank you for your help.