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.