将 SQL Server 结果集转换为字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4819412/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Convert SQL Server result set into string
提问by Zerotoinfinity
I am getting the result in SQL Server as
我在 SQL Server 中得到的结果是
SELECT StudentId FROM Student WHERE condition = xyz
I am getting the output like
我得到的输出像
StudentId 1236 7656 8990 ........
The output parameter of the stored procedure is @studentIdstring and I want the return statement as
存储过程的输出参数是@studentId字符串，我希望返回语句为
1236, 7656, 8990.
How can I convert the output in the single string?
如何转换单个字符串中的输出？
I am returning single column [ie. StudentId]
我正在返回单列 [即。学生卡]
回答by Bonshington
DECLARE @result varchar(1000)
SELECT @result = ISNULL(@result, '') + StudentId + ',' FROM Student WHERE condition = xyz
select substring(@result, 0, len(@result) - 1) --trim extra "," at end
回答by ABI
Test this:
测试这个：
DECLARE @result NVARCHAR(MAX)
SELECT @result = STUFF(
( SELECT ',' + CONVERT(NVARCHAR(20), StudentId)
FROM Student
WHERE condition = abc
FOR xml path('')
)
, 1
, 1
, '')
回答by Saloni
Use the COALESCEfunction:
使用COALESCE函数：
DECLARE @StudentID VARCHAR(1000)
SELECT @StudentID = COALESCE(@StudentID + ',', '') + StudentID
FROM Student
WHERE StudentID IS NOT NULL and Condition='XYZ'
select @StudentID
回答by Alex
Both answers are valid, but don't forget to initializate the value of the variable, by default is NULL and with T-SQL:
两个答案都是有效的，但不要忘记初始化变量的值，默认情况下为 NULL 并且使用 T-SQL：
NULL + "Any text" => NULL
It's a very common mistake, don't forget it!
这是一个非常常见的错误，不要忘记它！
Also is good idea to use ISNULL function:
使用 ISNULL 函数也是个好主意：
SELECT @result = @result + ISNULL(StudentId + ',', '') FROM Student
回答by pistol-pete
Use the CONCATfunction to avoid conversion errors:
使用CONCAT函数避免转换错误：
DECLARE @StudentID VARCHAR(1000)
SELECT @StudentID = CONCAT(COALESCE(@StudentID + ',', ''), StudentID)
FROM Student
WHERE StudentID IS NOT NULL and Condition='XYZ'
select @StudentID
回答by Vadym Voznyuk
The answerfrom brad.vis incorrect! It won't give you a concatenated string.
Here's the correct code, almost like brad.v's but with one important change:
这是正确的代码，几乎就像brad.v的，但有一个重要的变化：
DECLARE @results VarChar(1000)
SELECT @results = CASE
WHEN @results IS NULL THEN CONVERT( VarChar(20), [StudentId])
ELSE @results + ', ' + CONVERT( VarChar(20), [StudentId])
END
FROM Student WHERE condition = abc;
See the difference? :) brad.vplease fix your answer, I can't do anything to correct it or comment on it 'cause my reputation here is zero. I guess I can remove mine after you fix yours. Thanks!
看到不同？:) brad.v请修正你的答案，我无法纠正或评论它，因为我在这里的声誉为零。我想在你修好你的之后我可以移除我的。谢谢！
回答by greenhouse
The following is a solution for MySQL (not SQL Server), i couldn't easily find a solution to this on stackoverflow for mysql, so i figured maybe this could help someone...
以下是 MySQL（不是 SQL Server）的解决方案，我无法在 mysql 的 stackoverflow 上轻松找到解决方案，所以我想也许这可以帮助某人...
ref: https://forums.mysql.com/read.php?10,285268,285286#msg-285286
参考：https: //forums.mysql.com/read.php?10,285268,285286#msg- 285286
original query...
原始查询...
SELECT StudentId FROM Student WHERE condition = xyz
SELECT StudentId FROM Student WHERE condition = xyz
original result set...
原始结果集...
StudentId
1236
7656
8990
new query w/ concat...
带有连接的新查询...
SELECT group_concat(concat_ws(',', StudentId) separator '; ')
FROM Student
WHERE condition = xyz
concat string result set...
连接字符串结果集...
StudentId
1236; 7656; 8990
note: change the 'separator' to whatever you would like
注意：将“分隔符”更改为您想要的任何内容
GLHF!
格鲁夫！
回答by Gega Kakabadze
This one works with NULL Values in Table and doesn't require substring operation at the end. COALESCE is not really well working with NULL values in table (if they will be there).
这个与表中的 NULL 值一起使用，并且最后不需要子字符串操作。COALESCE 不能很好地处理表中的 NULL 值（如果它们在那里）。
DECLARE @results VARCHAR(1000) = ''
SELECT @results = @results +
ISNULL(CASE WHEN LEN(@results) = 0 THEN '' ELSE ',' END + [StudentId], '')
FROM Student WHERE condition = xyz
select @results
回答by brad.v
or a single select statement...
或单个选择语句...
DECLARE @results VarChar(1000)
SELECT @results = CASE
WHEN @results IS NULL THEN CONVERT( VarChar(20), [StudentId])
ELSE ', ' + CONVERT( VarChar(20), [StudentId])
END
FROM Student WHERE condition = abc;
