在SQl中使用Stuff功能 [英] using Stuff function in SQl
问题描述
我需要用逗号分隔符(,)将多行值组合成单行。我从互联网上得到了一些例子,但仍然存在一些错误
从TempGroupEmailEmp中选择不同的','+ TempGroupEmailEmp.EmpName
执行上述查询的
显示以逗号开头的行值
从TempGroupEmailEmp中选择STUFF((选择distinct','+来自TempGroupEmailEmp的+ TempGroupEmailEmp.EmpName),1,2,''作为tmp
对于上面的查询显示以下错误
子查询返回的值超过1。当子查询遵循=,!=,<,< =,>,> =或子查询用作表达式时,不允许这样做。
任何建议
提前致谢
它有效我在查询中错过了for xml path('')
正确查询如下
从TempGroupEmailEmp中选择不同的STUFF((选择distinct','来自TempGroupEmailEmp的TempGroupEmailEmp.EmpName for XML path('')),1,1,'')作为tmp blockquote>
不要使用STUFF:
SELECT SUBSTRING((< span class =code-keyword> SELECT DISTINCT ' ,' + TempGroupEmailEmp.EmpName FROM TempGroupEmailEmp FOR XML PATH(' )), 2 , 100000 ) AS CSV
STUFF
适用于单行,但是你要在内部选择中选择所有行。
尝试将其替换为;
从TempGroupEmailEmp中选择STUFF(','+ TempGroupEmailEmp.EmpName,1,2,'')为tmp
(或者,更好的是,不要在EmpName
前加上',,然后删除它使用STUFF
。)
希望这有帮助,
弗雷德里克
Hi,
I need to combine multiple rows values into single row with comma separater (,). i had got example from internet and implemented but still some errors exists
select distinct ', ' + TempGroupEmailEmp.EmpName from TempGroupEmailEmp
executing above query displays rows values starting with comma
select STUFF((select distinct ', ' + TempGroupEmailEmp.EmpName from TempGroupEmailEmp),1,2,'') as tmp from TempGroupEmailEmp
for the above query displays the following error
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
any suggestions
Thanks in advance解决方案it works i missed "for xml path ('')" in the query
correct query as follows
select distinct STUFF((select distinct ', ' + TempGroupEmailEmp.EmpName from TempGroupEmailEmp for XML path('')),1,1,'') as tmp from TempGroupEmailEmp
Don't use STUFF:
SELECT SUBSTRING((SELECT DISTINCT ', ' + TempGroupEmailEmp.EmpName FROM TempGroupEmailEmp FOR XML PATH('')),2,100000) AS CSV
STUFF
works on a single row, but you're feeding it all the rows selected in the inner select.
Try replacing it with;
select STUFF(', ' + TempGroupEmailEmp.EmpName,1,2,'') as tmp from TempGroupEmailEmp
(Or, better yet, don't prefix theEmpName
with ', ' just to then remove it usingSTUFF
.)
Hope this helps,
Fredrik
这篇关于在SQl中使用Stuff功能的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!