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).