Cross Apply Sample

sELECT distinct a.Loc_Icardallotter, LEFT(el.EmpList,LEN(el.EmpList)-1)
FROM Mlocationwisealloter a
cross apply ( select loc_fullname +' , ' as [text()]
from Mlocationwisealloter b inner join mlocation on loc_code = b.loc_unitcd where b.loc_moduleno = a.loc_moduleno and b.Loc_Icardallotter = a.Loc_Icardallotter
order by loc_fullname
FOR XML PATH(''))el (EmpList)
WHERE (Loc_moduleno = 270)