How to concat all the entries of one column [SQL]

Question

As the title states, with the following SQL query:

SELECT "Name"
FROM "ExampleTable"

I get the following result:

Name
----------------
ExampleName1
ExampleName2
ExampleName3

Question: how to modify the query so that all the names are to be displayed in a row, so that they can be later used an array.

What I tried: FOR XML, STUFF - doesn't work

Expected result:

Name: ExampleName1, ExampleName2, ExampleName3.

Answer 1

If you are using SQL Server you can try this:

Select SUBSTRING( 
( 
     SELECT ',' + Name AS 'data()'
         FROM TableName FOR XML PATH('') 
), 2 , 1000) As Names

Check this on Fiddle: http://sqlfiddle.com/#!18/6ab0b3/6

Or may be an easy way using COALESCE that combines the multiple rows in single row separated by Comma separated values based on parameters passed:

Declare @val Varchar(MAX); 
Select @val = COALESCE(@val + ', ' + Name, Name) 
        From TableName Select @val;

select @val; // this will show all your data

Check this on fiddle: http://sqlfiddle.com/#!18/36112/2

Also, I am giving the screenshots for you to easily understand the steps if something goes wrong with the above screenshots.

Please note: In the above process, neither I have taken any PRIMARY KEY nor I am setting its auto-increment, but in the upcoming explanation I have used PRIMARY KEY as I have written everything in SSMS (Sql Server Management Studio)

Here is the schema for sample tblStudent table with PRIMARY KEY and auto increment set as you can see below:

Next enter some dummy values in it

finally write our query.