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?

• Matt Hamilton: For answers specific to SQL Server, try this question.
• Pykler: For MySQL, check out Group_Concat from this answer
• Pete Alvin: I wish the next version of SQL Server would offer a new feature to solve multi-row string concatination elegantly without the silliness of FOR XML PATH.
• Stack Man: Not SQL, but if this is a once-only thing, you can paste the list into this in-browser tool convert.town/column-to-comma-separated-list
• Richard: In Oracle you can use the LISTAGG(COLUMN_NAME) from 11g r2 before that there is an unsupported function called WM_CONCAT(COLUMN_NAME) which does the same.

Answer 1

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

• user140628: Great solution. The following may be helpful if you need to handle special characters like those in HTML: Rob Farley: Handling special characters with FOR XML PATH('').
• Sam: Apparently this doesn't work if the names contain XML characters such as < or &. See @BenHinman's comment.
• Bacon Bits: NB: This method is reliant on undocumented behavior of FOR XML PATH (''). That means it should not be considered reliable as any patch or update could alter how this functions. It's basically relying on a deprecated feature.
• Bacon Bits: @Whelkaholism The bottom line is that FOR XML is intended to generate XML, not concatenate arbitrary strings. That's why it escapes &, < and > to XML entity codes (&, <, >). I assume it also will escape " and ' to " and ' in attributes as well. It's not GROUP_CONCAT(), string_agg(), array_agg(), listagg(), etc. even if you can kind of make it do that. We should be spending our time demanding Microsoft implement a proper function.
• Jason C: Good news: MS SQL Server will be adding string_agg in v.Next. and all of this can go away.

Answer 2

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

• Graeme Perrow: To be clear, coalesce has nothing to do with creating the list, it just makes sure that NULL values are not included.
• user166390: @Graeme Perrow It doesn't exclude NULL values (a WHERE is required for that -- this will lose results if one of the input values is NULL), and it is required in this approach because: NULL + non-NULL -> NULL and non-NULL + NULL -> NULL; also @Name is NULL by default and, in fact, that property is used as an implicit sentinel here to determine if a ', ' should be added or not.
• fbarber: Please note that this method of concatenation relies on SQL Server executing the query with a particular plan. I have been caught out using this method (with the addition of an ORDER BY). When it was dealing with a small number of rows it worked fine but with more data SQL Server chose a different plan which resulted in selecting the first item with no concatenation whatsoever. See this article by Anith Sen.
• R. Schreurs: This method cannot be used as a sub query in a select list or where-clause, because it use a tSQL variable. In such cases you could use the methods offered by @Ritesh
• Marc Durdin: This is not a reliable method of concatenation. It is unsupported and should not be used (per Microsoft, e.g. support.microsoft.com/en-us/kb/287515, connect.microsoft.com/SQLServer/Feedback/Details/704389). It can change without warning. Use the XML PATH technique discussed in stackoverflow.com/questions/5031204/… I wrote more here: marc.durdin.net/2015/07/…

Answer 3

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;

• canon: And, unlike CLR solutions, you have control over the sorting.
• RuudvK: Is there a way to do sorting in case there is no GROUP BY (so for the "Without grouping" example)?
• RuudvK: Update: I managed to do the following, but is there a cleaner way? SELECT STRING_AGG(Name, ', ') AS Departments FROM ( SELECT TOP 100000 Name FROM HumanResources.Department ORDER BY Name) D
• Varun: I had to cast it to NVarchar(max) to get it work.. ``` SELECT STRING_AGG(CAST(EmpName as NVARCHAR(MAX)), ',') FROM EmpTable as t ```
• Yahya: I missed STRING_AGG all my life. This should be the accepted answer!

Answer 4

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

• Ben: holy s**t thats amazing! When executed on its own, as in your example the result is formatted as a hyperlink, that when clicked (in SSMS) opens a new window containing the data, but when used as part of a larger query it just appears as a string. Is it a string? or is it xml that i need to treat differently in the application that will be using this data?
• Lukáš Lánský: This approach also XML-escapes characters like < and >. So, SELECTing '<b>' + FName + '</b>' results in "&lt;b&gt;John&lt;/b&gt;&lt;b&gt;Paul..."
• Baodad: Neat solution. I am noticing that even when I do not add the + ', ' it still adds a single space between every concatenated element.
• NReilingh: @Baodad That appears to be part of the deal. You can workaround by replacing on an added token character. For example, this does a perfect comma-delimited list for any length: SELECT STUFF(REPLACE((SELECT '#!'+city AS 'data()' FROM #cityzip FOR XML PATH ('')),' #!',', '),1,2,'')
• illmortem: If you replace data() with text() it seems to generate the list without the need to trim spaces out.

Answer 5

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 [email protected]
2 NULL
3 [email protected], [email protected]

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

• David: Good use of the STUFF function to nix the leading two characters.
• R. Schreurs: I like this solution best, because I can easily use it in a select list by appending 'as <label>'. I am not sure how to do this with the solution of @Ritesh.
• BateTech: This is better than the accepted answer because this option also handles un-escaping XML reserverd characters such as <, >, &, etc. which FOR XML PATH('') will automatically escape.

Answer 6

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

• hardmooth: Works basically. Two things to consider: 1) if your column is not a CHAR, you need to cast it, e.g. via GROUP_CONCAT( CAST(id AS CHAR(8)) ORDER BY id ASC SEPARATOR ',') 2) if you have many values coming, you should increase the group_concat_max_len as written in stackoverflow.com/a/1278210/1498405
• Wilfred Almeida: This worked for me as of March 2022. I had url's in rows and wanted them as a single column and this worked. Thanks!
• GoldBishop: OP was about [MS] SQL Server

Answer 7

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

• EvilDr: This is really the best solution IMO as it avoids the encoding issues that FOR XML presents. I used Declare @Numbers AS Nvarchar(MAX) and it worked fine. Can you explain why you recommend not using it please?
• Andre Figueiredo: This solution has already been posted 8 years ago! stackoverflow.com/a/194887/986862
• Akmal Salikhov: Why is this query returns ??? symbols instead of Cyrillic ones? Is this just output issue?
• Developer Webs: @EvilDr You can avoid the XML encoding. See: stackoverflow.com/questions/15643683/…
• Peter Mortensen: Why not use the example from the question?

Answer 8

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;

• ProbablePrime: If you need more than one column, for example their employee id in brackets use the concat operator: select array_to_string(array_agg(name||'('||id||')'
• GoldBishop: Not applicable to sql-server, only to mysql

Answer 9

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.

• toscanelli: For older versions of Oracle, wm_concat is perfect. Its use is explained in the link gift by Alex. Thnks Alex!
• asgs: LISTAGG works perfect! Just read the document linked here. wm_concat removed from version 12c onwards.

Answer 10

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)

• knb: For the flabbergasted: this query inserts 12 rows (a 3 columns) into a temporary basetable, then creates a recursive Common Table Expression (rCTE) and then flattens the name column into a comma-separated string for 4 groups of ids. At first glance, I think this is more work than what most other solutions for SQL Server do.
• jmoreno: @knb: not sure if that is praise,condemnation,or just surprise. The base table is because I like my examples to actually work, it doesn't really have anything to do with the question.
• RET: I really like this solution (to iterate is human, to recurse divine!), but it will spontaneously combust if there are 100 or more entries to concatenate, due to a recursion limit in SQL Server. That's a landmine for the unwary...
• jmoreno: @RET: good point, it’s adjustable, but should be mentioned.

Answer 11

In SQL Server 2005 and later, use the query below to concatenate the rows.

DECLARE @t table
(
 Id int,
 Name varchar(10)
)
INSERT INTO @t
SELECT 1,'a' UNION ALL
SELECT 1,'b' UNION ALL
SELECT 2,'c' UNION ALL
SELECT 2,'d' 
SELECT ID,
stuff(
(
 SELECT ','+ [Name] FROM @t WHERE Id = t.Id FOR XML PATH('')
),1,1,'') 
FROM (SELECT DISTINCT ID FROM @t ) t

• Sam: I believe this fails when the values contain XML symbols such as < or &.
• Stritof: Works great as in examples provided. Instead of temporary table or variable I used CTE learn.microsoft.com/en-us/sql/t-sql/queries/…

Answer 12

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

Enter image description here

• whytheq: so much simpler than the old XML solution - thanks for sharing!
• JDIBO: Easy, fast, human readable. TOP!

Answer 13

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

• Marc Gravell: You'd need to init @names to something non-null, otherwise you will get NULL throughout; you'd also need to handle the delimiter (including the unnecessary one)
• ekkis: the only problem with this approach (which i use all the time) is that you can't embed it
• Tian van Heerden: To get rid of the leading space change the query to SELECT @names = @names + CASE WHEN LEN(@names)=0 THEN '' ELSE ' ' END + Name FROM Names
• Vita1ij: Also, you have to check that Name is not null, you can do it by doing: SELECT @names = @names + ISNULL(' ' + Name, '')

Answer 14

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 15

You need to create a variable that will hold your final result and select into it, like so.

Easiest Solution

DECLARE @char VARCHAR(MAX);
SELECT @char = COALESCE(@char + ', ' + [column], [column]) 
FROM [table];
PRINT @char;

Answer 16

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

Answer 17

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

• slayernoah: There is a space before the comma when using this :(
• Daniel Reis: Just replace ', ' with ',' if you don't want the extra space.

Answer 18

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(''))
 , ' ', ', ') 

• David Roussel: This is the best answer here in my opinon. The use of declare variable is no good when you need to join in another table, and this is nice and short. Good work.
• binball: that's not working good if FName data has spaces already, for example "My Name"
• Rejwanul Reja: Really it is working for me on ms-sql 2016 Select REPLACE( (select Name AS 'data()' from Brand Where Id IN (1,2,3,4) for xml path('')) , ' ', ', ') as allBrands

Answer 19

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

Answer 20

With the other answers, the person reading the answer must be aware of a specific domain table such as vehicle or student. The table must be created and populated with data to test a solution.

Below is an example that uses SQL Server "Information_Schema.Columns" table. By using this solution, no tables need to be created or data added. This example creates a comma separated list of column names for all tables in the database.

SELECT
 Table_Name
 ,STUFF((
 SELECT ',' + Column_Name
 FROM INFORMATION_SCHEMA.Columns Columns
 WHERE Tables.Table_Name = Columns.Table_Name
 ORDER BY Column_Name
 FOR XML PATH ('')), 1, 1, ''
 )Columns
FROM INFORMATION_SCHEMA.Columns Tables
GROUP BY TABLE_NAME 

Answer 21

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 22

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

• Josh: Thanks for this! I might suggest an edit to point out the importance of the GROUP BY

Answer 23

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)

• JT_: If you are deleting the last two symbols ', ', then you need to add ', ' after Name ('SELECT \@names = \@names + Name + ', ' FROM Names'). That way the last two chars will always be ', '.
• Tian van Heerden: In my case I needed to get rid of the leading comma so change the query to SELECT @names = @names + CASE WHEN LEN(@names)=0 THEN '' ELSE ', ' END + Name FROM Names then you don't have to truncate it afterwards.

Answer 24

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

Answer 25

To avoid null values you can use CONCAT()

DECLARE @names VARCHAR(500)
SELECT @names = CONCAT(@names, ' ', name) 
FROM Names
select @names

• Reversed Engineer: It would be nice to know why CONCAT works. A link to MSDN would be nice.

Answer 26

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.

• Protiguous: The DLL link is a 404 error. Using an assembly for this is overkill. See best answer for SQL Server.

Answer 27

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

Answer 28

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

Answer 29

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

Answer 30

For Oracle DBs, see this question: How can multiple rows be concatenated into one in Oracle without creating a stored procedure?

The best answer appears to be by @Emmanuel, using the built-in LISTAGG() function, available in Oracle 11g Release 2 and later.

SELECT question_id,
 LISTAGG(element_id, ',') WITHIN GROUP (ORDER BY element_id)
FROM YOUR_TABLE;
GROUP BY question_id

as @user762952 pointed out, and according to Oracle's documentation http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php, the WM_CONCAT() function is also an option. It seems stable, but Oracle explicitly recommends against using it for any application SQL, so use at your own risk.

Other than that, you will have to write your own function; the Oracle document above has a guide on how to do that.