I'm not sure what you want to achieve, but it sounds like you want to
concatenate rows into one row[
^], which will give you result like:
5 | 4,2
etc.
SELECT DISTINCT smanid, STUFF((SELECT ','+ CONVERT(VARCHAR(30), sid)
FROM [YourTable] AS S2
WHERE S1.smanid = S2.smanid
FOR XML PATH('')), 1, 1, '') AS Sids
FROM [YourTable] AS S1