SELECT OneTable.col1, OneTable.col2,
STUFF (
(
SELECT ' ' + mt.col1
FROM ManyTable mt
WHERE mt.foreignkeycol = OneTable.primarykeycol
FOR xml path('')
),
1, 1, ''
) AS concatenatedcol
FROM OneTable
Note: remember the '' in the inner SELECT statement, otherwise the concatenated column will contain XML tags (with the first bracket stripped).
