I recently attended a course for SQL Server programmers upgrading from SQL Server 2000 to 2005 (as we are at work). Naturally enough I thought that I’d know everything there was to know about the existing product but would find out about the new stuff in 2005. To my surprise we learnt a technique entirely by the way which has been there forever.

If you do this kind of thing:

DECLARE @v VARCHAR (1000)
SET @v = ''
SELECT @v = @v + a.x + ' ' FROM a WHERE a.y = z

then @v will be a space-separated string of all the values of the the x column in table a. I always assumed it would simply give me the first or the last or some arbitrary value.

You live and learn.