SQL Server - how to coalesce a string and add linefeeds?

Question

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.

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 on my system. IN SSMS, switch your view to "Results to Text" from "Results to Grid" and you will see the line breaks.

Answer 2

use the Char function to insert:

Tab char(9)

Line feed char(10)

Carriage return char(13)