Using SQL to convert row values to a single concatenated string

 A while ago I wrote an article about various ways to concatenate all the values in a set of rows into a single string value. Most solutions to this problem use some sort of looping or recursive method, but a recent forum posting alerted me to a relatively easy way to achieve this using a select statement.

To demonstrate this I’ll create a table and populate it :

CREATE TABLE Colours (ID INT, Description VARCHAR(10))

INSERT INTO Colours VALUES (1,'Red')
INSERT INTO Colours VALUES (2,'Orange')
INSERT INTO Colours VALUES (3,'Yellow')
INSERT INTO Colours VALUES (4,'Green')
INSERT INTO Colours VALUES (5,'Blue')

Then run the following code :

DECLARE @ColourList VARCHAR(100)
SELECT @ColourList=''

SELECT @ColourList=@ColourList + Description + ', '
FROM Colours ORDER BY ID
 
SELECT @ColourList

You can see that the @ColourList variable contains the desired concatenated value :

Red, Orange, Yellow, Green, Blue,