Use SQL instead of Delete and End from keyboard

Question

Hi,

I am running a simple statment below which gives the output as follow:

select '''' + name + '''' + ',' as Emp_Names from dbo.employee

Emp_Names 
'Jason', 
'Robert', 
'Celia', 
'Linda', 
'David', 
'James', 
'Alison', 
'Chris', 
'Mary',

Is there a way in SQL that can show my desired output as:

Emp_Names 
'Jason', 'Robert','Celia','Linda','David','James','Alison','Chris','Mary',

i can press a Delete and End together to get there but only for a handful records but not for a hundred records...

Thanks all!

i am using SQL Server 2005 +

Answer 1

You must state which SQL implementation or product you are using.

If you happen to be working in MySQL, you are looking for the GROUP_CONCAT aggregate function.

If you happen to be working in R:Base, you are looking for the LISTOF aggregate function.

Answer 2

Yes but it depends on which database are you using?

In SQL Server 2005 or later, you can use the stuff function if you want all the names in one column.

SELECT STUFF(( SELECT DISTINCT TOP 100 PERCENT ',' + Name FROM employee ORDER BY ',' +Name FOR XML PATH('') ), 1, 1, '') or

select STUFF(( SELECT DISTINCT TOP 100 PERCENT ''',''' + Name FROM employee ORDER BY ''',''' + Name FOR XML PATH('') ), 1, 2, '') + ''''
Otherwise you could use the pivot command to have each name as its own column. The only thing with using the pivot command is that you need to know the names before hand or else you would use it in conjunction with the stuff function.

Answer 3

Here you can see how to implement the equivalent to GROUP_CONCAT() of MySQL (which seems to be what you need) on SQL Server.

http://explainextended.com/2010/06/21/group_concat-in-sql-server/

It will concatenate the results of each group in a single comma separated string. For this use case, your query would end up being a lot simple than the example there, mainly because you don't need to group.

Answer 4

Gads, group_concat-in-sql-server is certainly harder than how I would do it:

1> select sum(ID), count(*) from #b
2> go

 ----------- -----------
          41           7

1> declare @i varchar(60) 
   select @i = coalesce(@i, '') + ltrim(str(ID)) + ', ' 
   from #b 
   select @i
2> go

 ------------------------------------------------------------
 2, 3, 4, 6, 7, 8, 11,                                       

Extra credit for losing the final comma....