Recently I’m working on a project in which I got a task where I have to concatenate multiple rows into a single string of text for showing customer detail in Product Receipt. We can do this task in multiple ways to concatenate a column into a string. In this post, i have explained some common and best techniques to achieve this task.

For that, I have created a table named “City” .

How to concatenate text from multiple rows into a single text

 

Now i want to concat my rows into a single string like Row1Value, Row2Value, Row3Value,Row4Value………..RownValue.

You can use COALESCE function for Concatenating rows into a single string using. For using COALESCE method you should have SQL Server version 2008 and higher.

COALESCE 

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

STRING_AGG

You can use STRING_AGG function for Concatenating rows into a single string using. For using STRING_AGG method you should have SQL Server version 2017 and higher.

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

if you have an older version of SQL Server version 2005 and higher.The easiest way to concatenate Multiple rows into a comma string value is to use FOR XML PATH in a SQL select query.

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

Result:-

result

 

The post [Solved]-How to concatenate text from multiple rows into a single text string in SQL server? appeared first on Software Development | Programming Tutorials.



Read More Articles