SELECT
[ItemCode],
COUNT([ProjectId]) as ProjectsCnt,
min(
STUFF((
SELECT ','+[ProjectId] FROM [ItemCodes]
WHERE [ProjectId]=C.[ProjectId]
GROUP BY [ItemCode]
FOR XML PATH('')
) , 1 , 1 , '' )
) AS ProjectIds
FROM [ItemCodes] C
GROUP BY [ItemCode]