The standard way is something like the following:
SELECT DISTINCT [State]
, Stuff(
( SELECT ', ' + City
FROM Table1 AS t2
WHERE t1.[State] = t2.[State]
ORDER BY City
FOR XML PATH('')
)
, 1 -- start position
, 2 -- number of characters
, '' -- replacement string
) AS Cities
FROM Table1 AS t1
Of course, this has to scan Table1 twice, so if you have a table that already contains distinct states, it would be better to use that for the outer query.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA