CODEWITHSUNDEEP
sqlsql-server
Nick Heidke
Nick Heidke

Reputation: 2847

Append a New Line onto each concatenated row from SQL Server

The following sql query correctly concatenates each row of the table for a given foreign key PlanId. However, The CHAR(10) + CHAR(13) appear to be ignored.

SELECT PlanID,
   STUFF(
            (
                SELECT CONVERT(NVARCHAR, CONVERT(DATE, CreateTimestamp)) + ' ' + CreateUserID + ': ' + Notes
                       + CHAR(13) + CHAR(10)
                FROM PlanNotes
                WHERE PlanID = OuterNotes.PlanId
                ORDER BY UpdateTimestamp DESC
                FOR XML PATH(''), TYPE
            ).value('.', 'nvarchar(max)'),
            1,
            0,
            ''
        ) AS Notes
FROM PlanNotes OuterNotes;

The resulting output looks like:

2017-12-04 nickh: Nick's Test Notes v2 2017-11-27 bobj: Original date was 6/1/2016.

When I want:

2017-12-04 nickh: Nick's Test Notes v2
2017-11-27 bobj: Original date was

Upvotes: 2

Views: 215

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

My guess is that the CHAR(10) and CHAR(13) characters did in fact make it into your output, but that for whatever reason SSMS or your particular tool is not rendering them as you would expect. A workaround here might be to output your query to a text file and then open in a bona-fide text editor (e.g. Notepad++).

Upvotes: 1

Related Questions