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

If you are on SQL Server 2017 or Azure, see Mathieu Renda answer.

I had a similar issue when I was trying to join two tables with one-to-many relationships. In SQL 2005 I found that XML PATH method can handle the concatenation of the rows very easily.

If there is a table called STUDENTS

SubjectID       StudentName
----------      -------------
1               Mary
1               John
1               Sam
2               Alaina
2               Edward

Result I expected was:

SubjectID       StudentName
----------      -------------
1               Mary, John, Sam
2               Alaina, Edward

I used the following T-SQL:

SELECT Main.SubjectID,
       LEFT(Main.Students,Len(Main.Students)-1) As "Students"
FROM
    (
        SELECT DISTINCT ST2.SubjectID, 
            (
                SELECT ST1.StudentName + ',' AS [text()]
                FROM dbo.Students ST1
                WHERE ST1.SubjectID = ST2.SubjectID
                ORDER BY ST1.SubjectID
                FOR XML PATH (''), TYPE
            ).value('text()[1]','nvarchar(max)') [Students]
        FROM dbo.Students ST2
    ) [Main]

You can do the same thing in a more compact way if you can concat the commas at the beginning and use substring to skip the first one so you don't need to do a sub-query:

SELECT DISTINCT ST2.SubjectID, 
    SUBSTRING(
        (
            SELECT ','+ST1.StudentName  AS [text()]
            FROM dbo.Students ST1
            WHERE ST1.SubjectID = ST2.SubjectID
            ORDER BY ST1.SubjectID
            FOR XML PATH (''), TYPE
        ).value('text()[1]','nvarchar(max)'), 2, 1000) [Students]
FROM dbo.Students ST2

This answer may return unexpected results For consistent results, use one of the FOR XML PATH methods detailed in other answers.

Use COALESCE:

DECLARE @Names VARCHAR(8000) 
SELECT @Names = COALESCE(@Names + ', ', '') + Name 
FROM People

Just some explanation (since this answer seems to get relatively regular views):

• Coalesce is really just a helpful cheat that accomplishes two things:

1) No need to initialize @Names with an empty string value.

2) No need to strip off an extra separator at the end.

• The solution above will give incorrect results if a row has a NULL Name value (if there is a NULL, the NULL will make @Names NULL after that row, and the next row will start over as an empty string again. Easily fixed with one of two solutions:

DECLARE @Names VARCHAR(8000) 
SELECT @Names = COALESCE(@Names + ', ', '') + Name
FROM People
WHERE Name IS NOT NULL

or:

DECLARE @Names VARCHAR(8000) 
SELECT @Names = COALESCE(@Names + ', ', '') + 
    ISNULL(Name, 'N/A')
FROM People

Depending on what behavior you want (the first option just filters NULLs out, the second option keeps them in the list with a marker message [replace 'N/A' with whatever is appropriate for you]).

In MySQL, there is a function, GROUP_CONCAT(), which allows you to concatenate the values from multiple rows. Example:

SELECT 1 AS a, GROUP_CONCAT(name ORDER BY name ASC SEPARATOR ', ') AS people 
FROM users 
WHERE id IN (1,2,3) 
GROUP BY a

Oracle 11g Release 2 supports the LISTAGG function. Documentation here.

COLUMN employees FORMAT A50

SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
FROM   emp
GROUP BY deptno;

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

3 rows selected.

Warning

Be careful implementing this function if there is possibility of the resulting string going over 4000 characters. It will throw an exception. If that's the case then you need to either handle the exception or roll your own function that prevents the joined string from going over 4000 characters.

I don't have access to a SQL Server at home, so I'm guess at the syntax here, but it's more or less:

DECLARE @names VARCHAR(500)

SELECT @names = @names + ' ' + Name
FROM Names

In SQL Server 2017 or later versions, you can use the STRING_AGG() function to generate comma-separated values. Please have a look below at one example.

SELECT
    VendorId, STRING_AGG(FirstName,',') UsersName
FROM Users
WHERE VendorId != 9
GROUP BY VendorId

This worked for me (SQL Server 2016):

SELECT CarNamesString = STUFF((
         SELECT ',' + [Name]
            FROM tbl_cars
            FOR XML PATH('')
         ), 1, 1, '')

Here is the source: https://www.mytecbits.com/

And a solution for MySQL (since this page show up in Google for MySQL):

SELECT [Name],
       GROUP_CONCAT(DISTINCT [Name]  SEPARATOR ',')
       FROM tbl_cars

From MySQL documentation.

SELECT STUFF((SELECT ', ' + name FROM [table] FOR XML PATH('')), 1, 2, '')

Here's a sample:

DECLARE @t TABLE (name VARCHAR(10))
INSERT INTO @t VALUES ('Peter'), ('Paul'), ('Mary')
SELECT STUFF((SELECT ', ' + name FROM @t FOR XML PATH('')), 1, 2, '')
--Peter, Paul, Mary

DECLARE @Names VARCHAR(8000)
SELECT @name = ''
SELECT @Names = @Names + ',' + Names FROM People
SELECT SUBSTRING(2, @Names, 7998)

This puts the stray comma at the beginning.

However, if you need other columns, or to CSV a child table you need to wrap this in a scalar user defined field (UDF).

You can use XML path as a correlated subquery in the SELECT clause too (but I'd have to wait until I go back to work because Google doesn't do work stuff at home :-)

On top of Chris Shaffer's answer:

If your data may get repeated, such as

Tom
Ali
John
Ali
Tom
Mike

Instead of having Tom,Ali,John,Ali,Tom,Mike

You can use DISTINCT to avoid duplicates and get Tom,Ali,John,Mike:

DECLARE @Names VARCHAR(8000)
SELECT DISTINCT @Names = COALESCE(@Names + ',', '') + Name
FROM People
WHERE Name IS NOT NULL
SELECT @Names

This method applies to the Teradata Aster database only as it uses its NPATH function.

Again, we have table Students

SubjectID       StudentName
----------      -------------
1               Mary
1               John
1               Sam
2               Alaina
2               Edward

Then with NPATH it is just single SELECT:

SELECT * FROM npath(
  ON Students
  PARTITION BY SubjectID
  ORDER BY StudentName
  MODE(nonoverlapping)
  PATTERN('A*')
  SYMBOLS(
    'true' as A
  )
  RESULT(
    FIRST(SubjectID of A) as SubjectID,
    ACCUMULATE(StudentName of A) as StudentName
  )
);

Result:

SubjectID       StudentName
----------      -------------
1               [John, Mary, Sam]
2               [Alaina, Edward]

One way you could do it in SQL Server would be to return the table content as XML (for XML raw), convert the result to a string and then replace the tags with ", ".

In PostgreSQL - array_agg

SELECT array_to_string(array_agg(DISTINCT rolname), ',') FROM pg_catalog.pg_roles;

Or STRING_AGG

SELECT STRING_AGG(rolname::text,',') FROM pg_catalog.pg_roles;

With the 'TABLE' type it is extremely easy. Let's imagine that your table is called Students and it has column name.

declare @rowsCount INT
declare @i INT = 1
declare @names varchar(max) = ''

DECLARE @MyTable TABLE
(
  Id int identity,
  Name varchar(500)
)
insert into @MyTable select name from Students
set @rowsCount = (select COUNT(Id) from @MyTable)

while @i < @rowsCount
begin
 set @names = @names + ', ' + (select name from @MyTable where Id = @i)
 set @i = @i + 1
end
select @names

This example was tested with SQL Server 2008 R2.

With a recursive query you can do it:

-- Create example table
CREATE TABLE tmptable (NAME VARCHAR(30)) ;

-- Insert example data
INSERT INTO tmptable VALUES('PETER');
INSERT INTO tmptable VALUES('PAUL');
INSERT INTO tmptable VALUES('MARY');

-- Recurse query
with tblwithrank as (
select * , row_number() over(order by name) rang , count(*) over() NbRow
from tmptable
),
tmpRecursive as (
select *, cast(name as varchar(2000)) as AllName from tblwithrank  where rang=1
union all
select f0.*,  cast(f0.name + ',' + f1.AllName as varchar(2000)) as AllName 
from tblwithrank f0 inner join tmpRecursive f1 on f0.rang=f1.rang +1 
)
select AllName from tmpRecursive
where rang=NbRow

We can use RECUSRSIVITY, WITH CTE, union ALL as follows

declare @mytable as table(id int identity(1,1), str nvarchar(100))
insert into @mytable values('Peter'),('Paul'),('Mary')

declare @myresult as table(id int,str nvarchar(max),ind int, R# int)

;with cte as(select id,cast(str as nvarchar(100)) as str, cast(0 as int) ind from @mytable
union all
select t2.id,cast(t1.str+',' +t2.str as nvarchar(100)) ,t1.ind+1 from cte t1 inner join @mytable t2 on t2.id=t1.id+1)
insert into @myresult select *,row_number() over(order by ind) R# from cte

select top 1 str from @myresult order by R# desc

First of all you should declare a table variable and fill it with your table data and after that, with a WHILE loop, select row one by one and add its value to a nvarchar(max) variable.

    Go
    declare @temp table(
        title nvarchar(50)
    )
    insert into @temp(title)
    select p.Title from dbo.person p
    --
    declare @mainString nvarchar(max)
    set @mainString = '';
    --
    while ((select count(*) from @temp) != 0)
    begin
        declare @itemTitle nvarchar(50)
        set @itemTitle = (select top(1) t.Title from @temp t)
    
        if @mainString = ''
        begin
            set @mainString = @itemTitle
        end
        else
        begin
            set @mainString = concat(@mainString,',',@itemTitle)
        end
    
        delete top(1) from @temp
    
    end
    print @mainString