Carriage Return SQL Server

Question

Hey guys I am working on this query and I keep getting the output I want all in one line and I'd like it to be separated

the code is as follows:

SELECT e.clientcode,
       clientname,
       staffcode        AS ServiceEM,
       debttrandate     AS InvoiceDate,
       debttranrefalpha AS InvoiceNbr,

       dd.feenarrative  AS narrative,

       dd.amount        AS BillAmount
FROM   tblengagement e
       INNER JOIN tblclientservices cs ON cs.contindex = e.contindex
       INNER JOIN tblstaff s ON s.staffindex = cs.servmanager
       INNER JOIN tbltrandebtor d ON d.contindex = e.contindex AND d.contindex = cs.contindex
       INNER JOIN tbltrandebtordetail dd ON dd.debttranindex = d.debttranindex AND dd.debtdetservice = cs.servindex

WHERE  dd.debtdetservice = 'taxcomp' AND 
        d.DebtTranType=3 and 
        DebtTranDate between 'jan 1 2014' and 'oct 31 2014' 
        and DebtTranRefAlpha='72598'

right now column dd.feenarative gives an output such as Aaa....BBB...CCC...DDD all in one line on the first row instead of it displaying

aaa

bbb

ccc

ddd

I know I need to do some sort of Carriage Return but I have tried everything such as adding declare @crlf varchar(2000) before the select and

CAST(+dd.FeeNarrative as varchar(2000)),+ ' ' +Cast( dd.FeeNarrative as varchar(2000)) + ''

in the select but I doesn't accomplish it...I played around with it all different ways.

You're selecting four columns, and that's what you're going to get as output. It's one row of data, not four. Where do you want to display the output? It has nothing to do with carriage returns - it's returning exactly what your query tells it to return. — Ken White, Nov 20 '14 at 23:38
does it contain `char(13)+Char(10)` or are you talking about a problem with the MSSMS — bummi, Nov 20 '14 at 23:38

score 0 · Accepted Answer · edited May 23 '17 at 12:27

The output data shows that it is single record. Even if you have carriage return data, sqlserver display as a continue string. What I suggest, you can check at ui level.

If still you have issue, then you can use CHAR(10) + CHAR(13) combination for CR. or you can use <\br> or \n to show in html.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/9e53979f-e477-49da-b6b0-ddbf70735320/carriage-returnline-feedchar10-and-char13-help?forum=transactsql

carriage return in sql server 2012

New line in sql server 2008 r2

Use of REPLACE in SQL Query for newline/ carriage return characters

Carriage Return SQL Server

1 Answers1