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

Question

Consider a database table holding names, with three rows:

Peter
Paul
Mary

Is there an easy way to turn this into a single string of Peter, Paul, Mary?

Answer 1

Here I am showing about if it is more than 2 columns table data, then how to solve the problem.

If you have SQL Server Management Studio 2017 or later, you can very easily solve it using

STRING_AGG

Concatenates the values of string expressions and places separator values between them. The separator isn't added at the end of string.

Below is the SQL query

DECLARE @tbl TABLE (id INT, _name CHAR(1), days VARCHAR(10), daysfrequency VARCHAR(10), scheduletime VARCHAR(10));
INSERT INTO @tbl (id,_name,days,daysfrequency,scheduletime) VALUES
(1, 'a', 'Day4','Monthly','22:10'),
(1, 'a', 'Thu', 'Weekly', '07:30'),
(1, 'a', 'Fri', 'Daily',  '23:10'),
(2, 'b', 'Mon', 'Weekly', '20:00'),
(2, 'b', 'Tue', 'Weekly', '23:10'),
(2, 'b', 'Wed', 'Weekly', '18:10'),
(2, 'b', 'Thu', 'Weekly', '10:23'),
(2, 'b', 'Fri', 'Weekly', '1:23');


SELECT  t.id,
        t._name,
        STRING_AGG(t.days, ', ') WITHIN GROUP (ORDER BY t._name ASC) AS days,
        t1.daysfrequency,
        STRING_AGG(t.scheduletime, ', ') WITHIN GROUP (ORDER BY t._name ASC) AS scheduletime
FROM @tbl t INNER JOIN
(
    SELECT id, _name, STRING_AGG(daysfrequency, ', ') WITHIN GROUP (ORDER BY _name ASC) AS daysfrequency FROM
    (
        SELECT DISTINCT id, _name, daysfrequency FROM @tbl
    ) a
    GROUP BY id, _name
) t1
ON t.id = t1.id AND t._name = t1._name
GROUP BY t.id, t._name, t1.daysfrequency;

Answer 2

It's now 2024, and a lot of the Answers shown here from previous years are a bit out-of-date. With SQL Server, you can now easily combine fields using STRING_AGG.

Let's look at a simple example.

In my database, I have a table of Users, a table of possible Roles, and a third table showing which Users have which Roles.

Here's how I would get a list of user names, followed by a comma-seperated list of their roles:

SELECT usr.Id, usr.UserName, STRING_AGG(rol.Name, ', ') AS 'Roles'
FROM dbo.[User] usr
LEFT JOIN [dbo].[RoleUser] ru
ON ru.UserId = usr.Id
LEFT JOIN [dbo].[Role] rol
ON rol.Id = ru.RoleId
GROUP BY usr.Id, usr.UserName
ORDER BY usr.Id

And here's what it would look like:

Answer 3

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 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
        GROUP BY ST2.SubjectID
    ) [Main]

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

SELECT ST2.SubjectID, 
    STUFF(
        (
            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)'), 1, 1, '') [Students]
FROM dbo.Students ST2
GROUP BY ST2.SubjectID

Answer 4

I don't have the reputation to comment on the answers warning about using varchar(max), so posting as an answer with full supporting documentation.

If your variable is defined such as varchar(max), you will run into performance issues if the resulting variable length is greater than 256k. It'll append each value in <2 ms up to that point when utilized with a table select, then the updates will take 200x times as long each time you append once you cross that 256k threshold, increasing in time as the size increases.

To work around that limit, create a temp table that you insert rows into instead, then use the XML method covered in many other posts to create the resulting value.

Demonstrating the performance problem:

-- Takes < 45 sec to run to build a variable 512k in size using the variable appending method
SET NOCOUNT ON
DECLARE @txt nvarchar(max)='',
        @StartTime datetime2,
        @MS int,
        @k int
DECLARE @Stats TABLE (
    id int NOT NULL IDENTITY PRIMARY KEY,
    k int,
    ms int
)

WHILE Len(@txt) / 1024 < 512
BEGIN
    SET @StartTime=SYSDATETIME()
    SELECT TOP 28 @txt=@txt + cast(rowguid as char(36))
    FROM AdventureWorks2022.Person.Person
    SET @MS=DATEDIFF(ms,@StartTime,SYSDATETIME())
    SET @k=Len(@txt) / 1024
    IF NOT EXISTS (SELECT 1 FROM @Stats WHERE k=@k)
        INSERT INTO @Stats VALUES (@k,@MS)
END

SELECT ms FROM @Stats ORDER BY id

The below SQL creates a variable with over a MB of data ranging from 80ms to 150ms total on the same laptop as the above query. You will have to unencode any < and > and check for any other character encoding in your result. If this process is inside a loop, be sure to delete all rows from the temp table after extracting it's data. Also note your delimiter will have a trailing space added to it.

-- Takes < 1 sec to run
SET NOCOUNT ON
DECLARE @txt nvarchar(max)='',
    @StartTime datetime2,
    @MS int
DROP TABLE IF EXISTS #Hold
CREATE TABLE #Hold (
    id int NOT NULL IDENTITY PRIMARY KEY,
    txt nvarchar(102)
)

SET @StartTime=SYSDATETIME()

INSERT INTO #Hold
SELECT cast(rowguid as char(36)) + N','
FROM AdventureWorks2022.Sales.SalesOrderHeader

SET @txt=(SELECT txt AS 'data()' FROM #Hold ORDER BY id FOR XML PATH(''))

SET @MS=DATEDIFF(ms,@StartTime,SYSDATETIME())

SELECT @MS as MS, Len(@txt) as TxtLen

Answer 5

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/

For newer SQL versions (finally implemented)

SELECT STRING_AGG(Name, ', ') AS CarNames
FROM tbl_TypeCar;

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.

Answer 6

In SQL Server 2017 or later versions, you can concatenate text from multiple rows into a single text string in SQL Server using the STRING_AGG function. Here's an example of how you can achieve this:

Assuming you have a table called "Names" with a column "Name" containing the values Peter, Paul, and Mary in separate rows, you can use the following SQL query:

SELECT STRING_AGG(Name, ', ') AS ConcatenatedNames
FROM Names;

This query will return a single string with the names concatenated and separated by commas:

ConcatenatedNames
-----------------
Peter, Paul, Mary

Answer 7

A recursive CTE solution was suggested, but no code was provided. The code below is an example of a recursive CTE.

Note that although the results match the question, the data doesn't quite match the given description, as I assume that you really want to be doing this on groups of rows, not all rows in the table. Changing it to match all rows in the table is left as an exercise for the reader.

;WITH basetable AS (
    SELECT
        id,
        CAST(name AS VARCHAR(MAX)) name,
        ROW_NUMBER() OVER (Partition BY id ORDER BY seq) rw,
        COUNT(*) OVER (Partition BY id) recs
    FROM (VALUES
        (1, 'Johnny', 1),
        (1, 'M', 2),
        (2, 'Bill', 1),
        (2, 'S.', 4),
        (2, 'Preston', 5),
        (2, 'Esq.', 6),
        (3, 'Ted', 1),
        (3, 'Theodore', 2),
        (3, 'Logan', 3),
        (4, 'Peter', 1),
        (4, 'Paul', 2),
        (4, 'Mary', 3)
    ) g (id, name, seq)
),
rCTE AS (
    SELECT recs, id, name, rw
    FROM basetable
    WHERE rw = 1

    UNION ALL

    SELECT b.recs, r.ID, r.name +', '+ b.name name, r.rw + 1
    FROM basetable b
    INNER JOIN rCTE r ON b.id = r.id AND b.rw = r.rw + 1
)
SELECT name
FROM rCTE
WHERE recs = rw AND ID=4
OPTION (MAXRECURSION 101)

Answer 8

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

Answer 9

PostgreSQL arrays are awesome. Example:

Create some test data:

postgres=# \c test
You are now connected to database "test" as user "hgimenez".
test=# create table names (name text);
CREATE TABLE
test=# insert into names (name) values ('Peter'), ('Paul'), ('Mary');
INSERT 0 3
test=# select * from names;
 name
-------
 Peter
 Paul
 Mary
(3 rows)

Aggregate them in an array:

test=# select array_agg(name) from names;
 array_agg
-------------------
 {Peter,Paul,Mary}
(1 row)

Convert the array to a comma-delimited string:

test=# select array_to_string(array_agg(name), ', ') from names;
 array_to_string
-------------------
 Peter, Paul, Mary
(1 row)

DONE

Since PostgreSQL 9.0 it is even easier, quoting from deleted answer by "horse with no name":

select string_agg(name, ',') 
from names;

Answer 10

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;

Answer 11

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

Answer 12

SQL Server 2017+ and SQL Azure: STRING_AGG

Starting with the next version of SQL Server, we can finally concatenate across rows without having to resort to any variable or XML witchery.

STRING_AGG (Transact-SQL)

Without grouping

SELECT STRING_AGG(Name, ', ') AS Departments
FROM HumanResources.Department;

With grouping:

SELECT GroupName, STRING_AGG(Name, ', ') AS Departments
FROM HumanResources.Department
GROUP BY GroupName;

With grouping and sub-sorting

SELECT GroupName, STRING_AGG(Name, ', ') WITHIN GROUP (ORDER BY Name ASC) AS Departments
FROM HumanResources.Department
GROUP BY GroupName;

Answer 13

In SQL Server vNext this will be built in with the STRING_AGG function. Read more about it in STRING_AGG (Transact-SQL).

Answer 14

Not that I have done any analysis on performance as my list had less than 10 items but I was amazed after looking through the 30 odd answers I still had a twist on a similar answer already given similar to using COALESCE for a single group list and didn't even have to set my variable (defaults to NULL anyhow) and it assumes all entries in my source data table are non blank:

DECLARE @MyList VARCHAR(1000), @Delimiter CHAR(2) = ', '
SELECT @MyList = CASE WHEN @MyList > '' THEN @MyList + @Delimiter ELSE '' END + FieldToConcatenate FROM MyData

I am sure COALESCE internally uses the same idea. Let’s hope Microsoft don't change this on me.

Answer 15

SQL Server 2005 or later

CREATE TABLE dbo.Students
(
    StudentId INT
    , Name VARCHAR(50)
    , CONSTRAINT PK_Students PRIMARY KEY (StudentId)
);

CREATE TABLE dbo.Subjects
(
    SubjectId INT
    , Name VARCHAR(50)
    , CONSTRAINT PK_Subjects PRIMARY KEY (SubjectId)
);

CREATE TABLE dbo.Schedules
(
    StudentId INT
    , SubjectId INT
    , CONSTRAINT PK__Schedule PRIMARY KEY (StudentId, SubjectId)
    , CONSTRAINT FK_Schedule_Students FOREIGN KEY (StudentId) REFERENCES dbo.Students (StudentId)
    , CONSTRAINT FK_Schedule_Subjects FOREIGN KEY (SubjectId) REFERENCES dbo.Subjects (SubjectId)
);

INSERT dbo.Students (StudentId, Name) VALUES
    (1, 'Mary')
    , (2, 'John')
    , (3, 'Sam')
    , (4, 'Alaina')
    , (5, 'Edward')
;

INSERT dbo.Subjects (SubjectId, Name) VALUES
    (1, 'Physics')
    , (2, 'Geography')
    , (3, 'French')
    , (4, 'Gymnastics')
;

INSERT dbo.Schedules (StudentId, SubjectId) VALUES
    (1, 1)        --Mary, Physics
    , (2, 1)    --John, Physics
    , (3, 1)    --Sam, Physics
    , (4, 2)    --Alaina, Geography
    , (5, 2)    --Edward, Geography
;

SELECT
    sub.SubjectId
    , sub.Name AS [SubjectName]
    , ISNULL( x.Students, '') AS Students
FROM
    dbo.Subjects sub
    OUTER APPLY
    (
        SELECT
            CASE ROW_NUMBER() OVER (ORDER BY stu.Name) WHEN 1 THEN '' ELSE ', ' END
            + stu.Name
        FROM
            dbo.Students stu
            INNER JOIN dbo.Schedules sch
                ON stu.StudentId = sch.StudentId
        WHERE
            sch.SubjectId = sub.SubjectId
        ORDER BY
            stu.Name
        FOR XML PATH('')
    ) x (Students)
;

Answer 16

Use COALESCE - Learn more from here

For an example:

102

103

104

Then write the below code in SQL Server,

Declare @Numbers AS Nvarchar(MAX) -- It must not be MAX if you have few numbers
SELECT  @Numbers = COALESCE(@Numbers + ',', '') + Number
FROM   TableName where Number IS NOT NULL

SELECT @Numbers

The output would be:

102,103,104

Answer 17

MySQL complete example:

We have users who can have much data and we want to have an output, where we can see all users' data in a list:

Result:

___________________________
| id   |  rowList         |
|-------------------------|
| 0    | 6, 9             |
| 1    | 1,2,3,4,5,7,8,1  |
|_________________________|

Table Setup:

CREATE TABLE `Data` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;


INSERT INTO `Data` (`id`, `user_id`) VALUES
(1, 1),
(2, 1),
(3, 1),
(4, 1),
(5, 1),
(6, 0),
(7, 1),
(8, 1),
(9, 0),
(10, 1);


CREATE TABLE `User` (
  `id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `User` (`id`) VALUES
(0),
(1);

Query:

SELECT User.id, GROUP_CONCAT(Data.id ORDER BY Data.id) AS rowList FROM User LEFT JOIN Data ON User.id = Data.user_id GROUP BY User.id

Answer 18

This answer will require some privilege on the server to work.

Assemblies are a good option for you. There are a lot of sites that explain how to create it. The one I think is very well explained is this one.

If you want, I have already created the assembly, and it is possible to download the DLL file here.

Once you have downloaded it, you will need to run the following script in your SQL Server:

EXEC sp_configure 'show advanced options', 1
RECONFIGURE;
EXEC sp_configure 'clr strict security', 1;
RECONFIGURE;

CREATE Assembly concat_assembly
   AUTHORIZATION dbo
   FROM '<PATH TO Concat.dll IN SERVER>'
   WITH PERMISSION_SET = SAFE;
GO

CREATE AGGREGATE dbo.concat (

    @Value NVARCHAR(MAX)
  , @Delimiter NVARCHAR(4000)

) RETURNS NVARCHAR(MAX)
EXTERNAL Name concat_assembly.[Concat.Concat];
GO

sp_configure 'clr enabled', 1;
RECONFIGURE

Observe that the path to assembly may be accessible to server. Since you have successfully done all the steps, you can use the function like:

SELECT dbo.Concat(field1, ',')
FROM Table1

---

Since SQL Server 2017 it is possible to use the STRING_AGG function.

Answer 19

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.

Answer 20

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]

Answer 21

There are a couple of ways in Oracle:

    create table name
    (first_name varchar2(30));

    insert into name values ('Peter');
    insert into name values ('Paul');
    insert into name values ('Mary');

Solution is 1:

    select substr(max(sys_connect_by_path (first_name, ',')),2) from (select rownum r, first_name from name ) n start with r=1 connect by prior r+1=r
    o/p=> Peter,Paul,Mary

Solution is 2:

    select  rtrim(xmlagg (xmlelement (e, first_name || ',')).extract ('//text()'), ',') first_name from name
    o/p=> Peter,Paul,Mary

Answer 22

I really liked elegancy of Dana's answer and just wanted to make it complete.

DECLARE @names VARCHAR(MAX)
SET @names = ''

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

-- Deleting last two symbols (', ')
SET @sSql = LEFT(@sSql, LEN(@sSql) - 1)

Answer 23

Use this:

ISNULL(SUBSTRING(REPLACE((select ',' FName as 'data()' from NameList for xml path('')), ' ,',', '), 2, 300), '') 'MyList'

Where the "300" could be any width taking into account the maximum number of items you think will show up.

Answer 24

One method not yet shown via the XML data() command in SQL Server is:

Assume a table called NameList with one column called FName,

SELECT FName + ', ' AS 'data()'
FROM NameList
FOR XML PATH('')

returns:

"Peter, Paul, Mary, "

Only the extra comma must be dealt with.

As adopted from @NReilingh's comment, you can use the following method to remove the trailing comma. Assuming the same table and column names:

STUFF(REPLACE((SELECT '#!' + LTRIM(RTRIM(FName)) AS 'data()' FROM NameList
FOR XML PATH('')),' #!',', '), 1, 2, '') as Brands

Answer 25

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

Answer 26

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

Answer 27

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]).

Answer 28

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

Answer 29

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

Answer 30

In SQL Server 2005

SELECT Stuff(
  (SELECT N', ' + Name FROM Names FOR XML PATH(''),TYPE)
  .value('text()[1]','nvarchar(max)'),1,2,N'')

---

In SQL Server 2016

you can use the FOR JSON syntax

i.e.

SELECT per.ID,
Emails = JSON_VALUE(
   REPLACE(
     (SELECT _ = em.Email FROM Email em WHERE em.Person = per.ID FOR JSON PATH)
    ,'"},{"_":"',', '),'$[0]._'
) 
FROM Person per

And the result will become

Id  Emails
1   abc@gmail.com
2   NULL
3   def@gmail.com, xyz@gmail.com

This will work even your data contains invalid XML characters

the '"},{"_":"' is safe because if you data contain '"},{"_":"', it will be escaped to "},{\"_\":\"

You can replace ', ' with any string separator

---

And in SQL Server 2017, Azure SQL Database

You can use the new STRING_AGG function