Join us in Las Vegas on June 13-16 for the Snowflake Summit

Learn More
SQL FAQ Feat

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

  • How-Tos FAQs
  • December 16, 2018

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

From 1 Month to 1 Hour! Create Your Data Models Faster in Datameer Then With SQL.

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?

More Resources We Think You Might Like

SQL FAQ Feat

How to avoid SQL injection in PHP?

SQL injection is one of the most common vulnerabilities in a web application. We can avoid it in ...

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

How to insert the result of a stored procedure ...

Let’s create a simple stored procedure (SP) with a SELECT query. There are different ways to inse...

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

How to INSERT values in a table using SELECT qu...

This can be done in below: INSERT INTO orders (ID, customer_name, order_date, total_orders) SELEC...

  • How-Tos FAQs
  • December 16, 2018