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

  • How-Tos FAQs
  • December 16, 2018
SQL FAQ Feat

Let us consider the following table:

CREATE TABLE fruits (
    ID INT,
    NAME VARCHAR(50)
)

INSERT INTO fruits
SELECT 1, 'Apple' UNION ALL
SELECT 2, 'Banana' UNION ALL
SELECT 3, 'Orange'

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?

Related Posts

SQL FAQ Feat

How to avoid SQL injection in PHP?

  • How-Tos FAQs
  • December 15, 2018