SQL Server - 如何合并字符串并添加换行符？
我想将一组数据选择到一个字符串中，但我仍然希望每个项目都在自己的行上（然后我将其放入水晶报表中，这比使用大量子报表更快。）
所以，我有以下数据：
ID Assessor
1 Black
1 Jones
我想查询它，并返回一个如下所示的单个字符串：
Black
Jones
如果我使用合并进行选择，我可以将其以逗号或分号分隔，但不能以换行分隔：
BEGIN
declare @Assessors NVarChar(max)
Declare @LineFeed varchar(10)
DECLARE @Return varchar(10)
Set @LineFeed = char(10)
SET @Return = char(13)
Select @Assessors = COALESCE(@Assessors + ', ', '') + a.Assessor
FROM dbo.Assessment a
Where (@ID = a.ID)
Return @Assessors
END
在这种情况下，该函数将返回“布莱克，琼斯”。但是，如果我将行更改为
Select @Assessors = COALESCE(@Assessors + @Return + @LineFeed, '') + a.Assessor
它会返回“Black Jones”——它不会插入换行符或返回符，而只是插入一个空格。
我想我不必使用 Coalesce，但我已经尝试过标准连接，但这也不会放入它。我现在已经将其放入函数中，但计划将其作为存储过程的一部分，因此速度更快。
I want to select a set of data into a single string, but I still want each item on its own line (I will then put it in Crystal Reports, and it will be faster than using tons of subreports.)
So, I have the following data:
ID Assessor
1 Black
1 Jones
and I want to query it, and return a single string that looks like:
Black
Jones
if I do a select using coalesce, I can make it comma or semi-colon delimited, but not linefeed delimited:
BEGIN
declare @Assessors NVarChar(max)
Declare @LineFeed varchar(10)
DECLARE @Return varchar(10)
Set @LineFeed = char(10)
SET @Return = char(13)
Select @Assessors = COALESCE(@Assessors + ', ', '') + a.Assessor
FROM dbo.Assessment a
Where (@ID = a.ID)
Return @Assessors
END
in that, the function will return 'Black, Jones'. But if I change the line to
Select @Assessors = COALESCE(@Assessors + @Return + @LineFeed, '') + a.Assessor
it returns 'Black Jones' -- it doesn't put in a linefeed or return, just a space.
I guess I don't have to use Coalesce, but I've tried just standard concatenating, and that won't put it in either. I've got this in a function right now, but plan on putting it as part of a stored procedure, so it's faster.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
CHAR(13)+CHAR(10)将产生换行符，我在生产代码中一直这样做。我运行了你的代码，它在我的系统上产生了换行符。在 SSMS 中，将视图从“结果到网格”切换到“结果到文本”，您将看到换行符。
CHAR(13)+CHAR(10)will produce line breaks, I do this all the time in production code.I ran your code, and it produces line breaks on my system. IN SSMS, switch your view to "Results to Text" from "Results to Grid" and you will see the line breaks.
使用Char函数插入：
Tab
char(9)
换行
char(10)
回车
字符(13)
use the Char function to insert:
Tab
char(9)
Line feed
char(10)
Carriage return
char(13)