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

For Oracle DBs, see this question:

[To see links please register here]

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

[To see links please register here]

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

This can be useful too

create table #test (id int,name varchar(10))
--use separate inserts on older versions of SQL Server
insert into #test values (1,'Peter'), (1,'Paul'), (1,'Mary'), (2,'Alex'), (3,'Jack')

DECLARE @t VARCHAR(255)
SELECT @t = ISNULL(@t + ',' + name, name) FROM #test WHERE id = 1
select @t
drop table #test

returns

Peter,Paul,Mary

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

To avoid null values you can use CONCAT()

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

declare @phone varchar(max)=''
select @phone=@phone + mobileno +',' from members
select @phone

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

SELECT PageContent = Stuff(
( SELECT PageContent
FROM dbo.InfoGuide
WHERE CategoryId = @CategoryId
AND SubCategoryId = @SubCategoryId
for xml path(''), type
).value('.[1]','nvarchar(max)'),
1, 1, '')
FROM dbo.InfoGuide info

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

<h1>Easiest Solution</h1>

DECLARE @char VARCHAR(MAX);

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

PRINT @char;

Although it's too late, and already has many solutions. Here is simple solution for MySQL:

SELECT t1.id,
GROUP_CONCAT(t1.id) ids
FROM table t1 JOIN table t2 ON (t1.id = t2.id)
GROUP BY t1.id

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