Q:

how to access Rolename from the group by query?

we having two table SiteRoles and SiteRolesModules . In SiteRolesModules we access RoleID in single row based on ModuleID . 
select t1.ModuleID,RoleID=( SELECT RoleID AS [data()]
FROM SiteRoleModules t2  
where t2.ModuleID = t1.ModuleID ORDER BY ModuleID  FOR XML PATH('')) 
from SiteRoleModules t1 GROUP BY ModuleID

In above image we got the output. We required Rolename instead of RoleID. RoleID and Rolename is present in SiteRoles table

In above image we have RoleID 1 - administrator, 2 - superAdmin, 3 - admin
we require output in image 1 Instead of RoleID 1 2 3 we need output as administrator superAdmin admin
ALTER PROCEDURE [dbo].[MEDEIL_SiteRoleModules_SelectOne] 
@ModuleID int
AS
SELECT
   SRM.RoleID,
   SR.RoleName
FROM
   [dbo].[SiteRoleModules]  SRM 
   INNER JOIN [dbo].[SiteRoles] SR 
   ON SR.RoleID = SRM.RoleID
WHERE ModuleID = 1

A:

Just you can use join to perform it;
select t1.ModuleID,RoleName=(  SELECT s1.RoleName AS [data()]

           FROM SiteRoleModules t2 inner join SiteRoles s1 ON t2.RoleID = s1.RoleID 
           where t2.ModuleID = t1.ModuleID group by s1.RoleName FOR XML PATH('')) from SiteRoleModules t1 GROUP BY ModuleID

