How to concatenate a row and add rows?

Question

I want to select a dataset in one row, but I still want each element in its row (I will then put it in Crystal Reports, and it will be faster than using a ton of subscriptions).

So, I have the following data:

ID Assessor 1 Black 1 Jones 

and I want to query it and return a single line that looks like this:

 Black Jones 

if I make a choice using coalesce, I can make it a comma or a comma separator, but not limit the line:

 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 

then the function will return "Black, Jones." But if I changed the line to

 Select @Assessors = COALESCE(@Assessors + @Return + @LineFeed, '') + a.Assessor 

it returns Black Jones - it does not put a string or returns, just a space.

I think I do not need to use Coalesce, but I tried only standard concatenation, and that also does not fit. I have this in a function right now, but I plan to put it as part of a stored procedure, so it's faster.

Answer 1

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 in my system. In SSMS, switch your view to Results to Text from Results to Grid, and you'll see line breaks.

Answer 2

use the char function to insert:

Tab char (9)

Line char (10)

Carriage return char (13)