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

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`?

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

I usually use select like this to concatenate strings in SQL Server:

with lines as
(
select
row_number() over(order by id) id, -- id is a line id
line -- line of text.
from
source -- line source
),
result_lines as
(
select
id,
cast(line as nvarchar(max)) line
from
lines
where
id = 1
union all
select
l.id,
cast(r.line + N', ' + l.line as nvarchar(max))
from
lines l
inner join
result_lines r
on
l.id = r.id + 1
)
select top 1
line
from
result_lines
order by
id desc

If you want to deal with nulls you can do it by adding a where clause or add another COALESCE around the first one.


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

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 ", ".

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 :-)

Using XML helped me in getting rows separated with commas. For the extra comma we can use the replace function of SQL Server. Instead of adding a comma, use of the AS 'data()' will concatenate the rows with spaces, which later can be replaced with commas as the syntax written below.

REPLACE(
(select FName AS 'data()' from NameList for xml path(''))
, ' ', ', ')

In Oracle, it is `wm_concat`. I believe this function is available in the [10g release][1] and higher.

[1]:

[To see links please register here]

A ready-to-use solution, with no extra commas:

select substring(
(select ', '+Name AS 'data()' from Names for xml path(''))
,3, 255) as "MyList"

An empty list will result in NULL value.
Usually you will insert the list into a table column or program variable: adjust the 255 max length to your need.

(Diwakar and Jens Frandsen provided good answers, but need improvement.)

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

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.

[1]:

[To see links please register here]