;WITH cte AS
(
	Select DISTINCT b.group_id, d.role_desc, c.role_cd, c.[admin], c.super_u, c.user_id, 
	a.lastname, a.fore_name, a.firstname, a.org, 
	a.job_title, a.active, a.last_on, a.lastupdate, 
	a.email_addr, c.date_on
	FROM san.dbo.[security] AS a
	INNER JOIN san.dbo.grpmems AS b ON a.user_id = b.grp_member
	LEFT JOIN tms.dbo.imso_security AS c ON a.user_id = c.user_id
	LEFT JOIN tms.dbo.[role] AS d ON c.role_cd = d.role_cd
	WHERE a.active = 1 and a.lastname Like @alpha 
)
SELECT DISTINCT
	b.Groups,
	a.*
FROM
	cte a
	OUTER APPLY
	(
		SELECT ','+b.group_id AS [text()]
		FROM cte b
		WHERE a.user_id = b.user_id
		FOR XML PATH('')
	) b(Groups)
ORDER BY
	a.lastname,
	a.firstname;