Jerry Nixon @Work: tSQL concatenate strings “1, 2, 3” and select results, too

Jerry Nixon on Windows

Saturday, April 10, 2010

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:

image

image

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