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,
Easy !
Before getting too excited I should add a disclaimer here. The solution relies on what appears to be an undocumented feature, i.e. that variable assignment is achieved by iterating around an entire resultset. I’ve been unable to confirm why it does it this way in the Microsoft documentation, but it does work on both SQL Server 2005 and 2008. If you are after a more bullet proof production solution you would probably be better off with a “traditional” solution involving a cursor, while loop or recursive CTE etc.
These other techniques are discussed in our more complete article which can be found here :
Converting row values in a table to a single concatenated string