Here is your Store Procedure :
create procedure Example1
As
begin
SELECT B.userid ,C.Name,
Roleid = STUFF ( ( SELECT ','+A.Roleid FROM Tbl2 A
WHERE A.userid = B.userid ORDER BY A.userid
FOR XML PATH(''),TYPE ).value('.','VARCHAR(MAX)')
, 1,1,SPACE(0)) FROM Tbl2 B, Tbl1 C
where B.userid=C.userid GROUP BY B.userid ,C.name
End
--Exec Example1
Output :
userid Name Roleid
1 X 1,2,3
2 Y 2,3