This answer may return unexpected results when an ORDER BY clause is present. 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]).

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

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

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

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

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;

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

Use COALESCE - Learn more from here

For an example:

102

103

104

Then write 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

Output would be:

102,103,104

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

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.

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

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

A recursive CTE solution was suggested, but no code 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

Starting with PostgreSQL 9.0 this is quite simple:

select string_agg(name, ',') 

from names;

In versions before 9.0 array_agg() can be used as shown by hgmnz

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;

In SQL Server vNext this will be built in with the STRING_AGG function, read more about it here:
https://msdn.microsoft.com/en-us/library/mt790580.aspx

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

         , ' ', ', ') 

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

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

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 

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.

I really liked elegancy of Dana's answer. 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)

To avoid null values you can use CONCAT()

DECLARE @names VARCHAR(500)

SELECT @names = CONCAT(@names, ' ', name) 

FROM Names

select @names

This answer will require some privilege in 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 here.

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

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

Hope it helps!!!

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

MySQL complete Example:

We have Users which can have many Data's and we want to have an output, where we can see all users Datas 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

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

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

This method applies to Teradata Aster database only as it utilizes 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]