select * from ColorBox
select
a.Code ,
STUFF((SELECT ','+rtrim(Color) FROM ColorBox WHERE a.Code = Code ORDER BY Code FOR XML PATH('')),1,1,'')
as Color,b.ccount as ColorCount
from ColorBox a
inner join (
select Code,count(color) as ccount from ColorBox group by Code
)b on b.Code = a.Code
group by a.Code,b.ccount
