my actual requirement is differnet
finaly i solved by taking the above examples
my final solution is
select
LEFT(SubGroupId ,LEN(SubGroupId ) - 1)
from
(
select
CONVERT(varchar(max),
(
select SubGroupId as [text()], ',' as [text()]
from CMSClientSubGroup csg WITH(NOLOCK)
INNER JOIN USER_ACCOUNT ua WITH(NOLOCK)
on csg.SubGroupId=ua.account_id
for xml path('')
)
)as result
) s