利用for xml 实现了。
SELECT b.ID_Role
, LEFT(b.user_id_list, LEN(b.user_id_list)-1) as user_id_list
, LEFT(b.user_name_list, LEN(b.user_name_list)-1) as user_name_list
into #temp_roleuserlist
FROM (
SELECT ID_Role,
(SELECT cast(ID_User as varchar(2000)) + ',' FROM #temp_roleuser
WHERE ID_Role = a.ID_Role
FOR XML PATH('')) AS user_id_list,
(SELECT Name_User + ',' FROM #temp_roleuser
WHERE ID_Role = a.ID_Role
FOR XML PATH('')) AS user_name_list
FROM #temp_roleuser a
GROUP BY ID_Role
) b