tSQL concatenate strings “1, 2, 3” and select results, too

An animal might have more than one color, and you might want to show the colors in a simple string like “Color1, Color2, Color3” using tSQL. This is simple with something like this:

DECLARE @x varchar(500)
SELECT IsNull(@x + ', ', '') + Name
FROM tb_Colors
WHERE AnimalFk = 1
PRINT @x

And although this is useful in many scenarios, if you want to select the user in an output that looks like this:

Animal  Colors
------  -------------------
Animal1 Color1, Color2
Animal2 Color2, Color3
Animal3 Color1, Color3

Before SQL 2005, you were in a pickle since SQL does not allow you to set variables AND select data in the same statement. But, now you can! Using a CTE, you can concatenate and select in a single statement.

Here’s how:

See the code here: http://codepaste.net/m117oh