How to concatenate text from multiple rows into a single text string in SQL Server

In SQL Server, you can concatenate text from multiple rows into a single text string using the FOR XML PATH clause and the STUFF function. Here's an example:

DECLARE @concatenatedString VARCHAR(MAX)

SELECT @concatenatedString = STUFF((SELECT ', ' + column_name

FROM table_name

FOR XML PATH('')), 1, 2, '')

SELECT @concatenatedString

In this example, the SELECT statement inside the STUFF function concatenates the values from the column_name in the table_name table, separated by a comma and a space. The FOR XML PATH('') clause is used to concatenate the values into a single string. The STUFF function then removes the first two characters (the comma and the space) from the string. Finally, the result is stored in the @concatenatedString variable.

For the SQL Server 2017 or higher version you can use the STRING_AGG() function, here is the example;

SELECT

      STRING_AGG(Coulmn_Name,',') col_nm

FROM Table1

Where Coulmn_Name is the column you want to concatenate value in a single text.