Use stuff and for xml path to select rows into a comma separated list.

concatenated list via stuff and for xml path

SELECT Stuff((SELECT ', ' + Name
        FROM StateProvinces
        FOR XML PATH ('')),1,1,'')