How to concatenate strings from mutliple rows into one in SQL Server 2000 or 2005

"How to concatenate (aka join) strings from mutliple rows into one in SQL Server 2000 or 2005". This is a very common question, so here is a simple answer:

   1:   

   2:  CREATE TABLE Colors 

   3:  ( 

   4:  Color VARCHAR(32) 

   5:  ) 

   6:  GO 

   7:  SET NOCOUNT ON 

   8:  INSERT Colors SELECT 'red' 

   9:  INSERT Colors SELECT 'orange' 

  10:  INSERT Colors SELECT 'blue' 

  11:  INSERT Colors SELECT 'green' 

  12:  GO 

  13:  DECLARE @colors VARCHAR(1024) 

  14:  SELECT 

  15:  @colors = COALESCE(@colors + ',', '') + Color 

  16:  FROM 

  17:  Colors 

  18:  SELECT Colors = @colors 

  19:  GO 

  20:  DROP TABLE Colors 

  21:  GO

Thanks to http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html