How to concatenate data from different rows of a table into a single variable in SQL?

Let us consider the following table:

We can concatenate text into a single text string using the below methods:

1.       Using ISNULL() function

DECLARE @fruits VARCHAR(1000)
DECLARE @separator VARCHAR(5) = '|' -- can be any separator like space (' '), comma(',') etc

SELECT @fruits = ISNULL(@fruits + @separator, '') + NAME
FROM fruits

SELECT @fruits as fruits

# Output

Fruits
---------------------
Apple|Banana|Orange

2.       Using FOR XML PATH

-- Using for xml path
SELECT STUFF((
    SELECT ',' + name
    FROM fruits
    FOR XML PATH(''))
    , 1, 1, ''
) AS fruits

#Output
fruits
------------------------------
Apple,Banana,Orange

3.       Using STRING_AGG() function for SQL Server 2017+

SELECT STRING_AGG(Name, ' ') AS fruits
FROM fruits

#Output
fruits
------------------------------------
Apple Banana Orange  

---

Up Next:

Read How to select the first row of each GROUP BY in SQL?