concatenate strings with COALESCE

This question is asked quite frequently

Color
------
red
orange
blue
green

And return a resultset like this:
Colors
-------------------------
red,orange,blue,green


This isn't exactly relational, and can certainly be handled by the presentation layer. However, there are kludges in SQL Server that will allow you to derive a solution. We'll start with the simple case above:

CREATE TABLE Colors
(
Color VARCHAR(32)
)
GO

SET NOCOUNT ON
INSERT Colors SELECT 'red'
INSERT Colors SELECT 'orange'
INSERT Colors SELECT 'blue'
INSERT Colors SELECT 'green'
GO

DECLARE @colors VARCHAR(1024)

SELECT
@colors = COALESCE(@colors + ',', '') + Color
FROM
Colors

SELECT Colors = @colors
GO

DROP TABLE Colors
GO