Cacher is the code snippet organizer for pro developers

select
  s.name,
  stuff ( 
    ( SELECT ',' + c.name AS [text()] FROM cities c where c.stateid=s.id 
      FOR XML PATH('') ),
    1, 1, '') cities
from states s;

select
  s.name,
  ( SELECT '<city>' + c.name + '</city>' AS [text()] FROM cities c where c.stateid=s.id 
    FOR XML PATH('') 
  ) cities
from states s;