Using SQL to convert row values to a single concatenated string

Category : Tips User Rating : 4.5 Stars      Views : 3.5 Stars
One Click Feedback
Please help us to improve the site by rating the quality of this article by clicking a button below.
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
Link back to this article : https://www.sqlmatters.com/Articles/Using SQL to convert row values to a single concatenated string.aspx

Keywords

TSQL,string


Comments
Post by Prashant on Tue 14 Apr 2015 06:39. Report Inappropriate Post

is it possible with charindex() Function Sir

Post a comment   No login required !

Name : Email : Website :
Will be displayed alongside your comment
Not displayed Optional, but displayed if entered