使用SQL而不是键盘上的Delete和End

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

我正在运行一个简单的语句,其中输出如下:

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

选择''''+ name +''''+','作为dbo.employee的Emp_Names

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

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

在SQL中是否有一种方法可以显示我想要的输出:

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 +

我正在使用SQL Server 2005 +

4 个解决方案

#1

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.

在SQL Server 2005或更高版本中,如果您想要一列中的所有名称,则可以使用stuff函数。

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 PER','+ Name FROM employee ORDER BY','+ Name for XML PATH('')),1,1,'')或

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.

选择STUFF((选择DISTINCT TOP 100%''','''+姓名来自员工ORDER BY''','''+名称为XML路径('')),1,2,'')+'' ''否则你可以使用pivot命令将每个名称作为自己的列。使用pivot命令的唯一方法是你需要事先知道名字,否则你会将它与stuff函数结合使用。

#2

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

您必须说明您正在使用哪个SQL实现或产品。

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

如果您正在使用MySQL,那么您正在寻找GROUP_CONCAT聚合函数。

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

如果您正好在R:Base工作,那么您正在寻找LISTOF聚合函数。

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

在这里,您可以看到如何在SQL Server上实现等效于SMB的GROUP_CONCAT()(这似乎是您需要的)。

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.

它将在一个逗号分隔的字符串中连接每个组的结果。对于这个用例,您的查询最终会比那里的示例简单,主要是因为您不需要分组。

#4

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

Gads,group_concat-in-sql-server肯定比我做的更难:

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

失去最后一个逗号的额外功劳......

#1