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