[Solved]-How to Concatenate Text from multiple rows into a single text string in SQL server?

Recently, I've been working on a project where I received a task requiring me to concatenate multiple rows into a single string of text to display customer details on a product receipt. There are several methods to accomplish this concatenation task effectively. In this post, I'll explain some common and best techniques to achieve this objective.

To demonstrate these techniques, I've created a table named 'City'.

Now, I want to concatenate my rows into a single string format such as Row1Value, Row2Value, Row3Value, Row4Value, and so on up to RownValue.

To achieve this, you can utilize the COALESCE function. However, it's essential to note that COALESCE method requires SQL Server version 2008 or higher.

COALESCE

DECLARE @CityNames VARCHAR(8000)
SELECT @CityNames = COALESCE(@CityNames + ', ', '') + CityName
FROM City
Select @CityNames;

STRING_AGG

You can utilize the STRING_AGG function to concatenate rows into a single string. However, it's important to note that the STRING_AGG method requires SQL Server version 2017 or higher.

SELECT STRING_AGG( ISNULL(CityName, ' '), ',') As CityNames
From City

STRING_AGG: An aggregate function available in SQL Server (starting from version 2017) that concatenates the values of a specified column using a specified separator.

ISNULL(CityName, ' '): This function checks if the value of the CityName column is NULL. If it is NULL, it replaces it with a space (' '). This is done to avoid having NULL values in the aggregated result.

As CityNames: Aliases the result of the STRING_AGG function as CityNames, so the resulting column in the output will be named CityNames.

From City: This specifies the table from which to retrieve the data, in this case, the table named City.

This SQL query selects all values from the CityName column of the City table, replaces any NULL values with a space, and then concatenates these values into a single string separated by commas. The result is a single column named CityNames containing a comma-separated list of city names.

If you have an older version of SQL Server, starting from version 2005 and higher, the simplest method to concatenate multiple rows into a comma-separated string value is by utilizing the FOR XML PATH in a SQL select query.

Select SUBSTRING(
(
SELECT ',' + CityName AS 'data()'
FROM City FOR XML PATH('')
), 2 , 9999) As AllCities

Result:-

SUBSTRING: Extract a substring from a string. In this case, it will extract a substring from the concatenated city names.

( ):Denote the beginning and end of the expression whose substring will be extracted.

(SELECT ',' + CityName AS 'data()' FROM City FOR XML PATH('')): A subquery that generates a comma-separated list of city names.

SELECT ',' + CityName AS 'data()': This part selects each city name from the City table and appends a comma (',') in front of it. The AS 'data()' alias is used to format the output of the subquery.

FOR XML PATH(''): This clause formats the result of the subquery as XML, with an empty string ('') specified as the PATH. This effectively concatenates the city names into a single string.

2: This is the starting position from which the substring will be extracted. It skips the first character, which is the initial comma (',') added in the subquery.

9999: This is the length of the substring to be extracted. It's set to a large value to ensure that the entire remaining string is extracted.

As AllCities: This aliases the resulting substring as AllCities, so the output column will be named AllCities.

This SQL query generates a comma-separated list of city names from the City table by concatenating them together with commas using a subquery. Then, it extracts a substring starting from the second character (to skip the initial comma) and continuing to the end of the string. Finally, it assigns this comma-separated list to a column named AllCities.