2

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

Nick Heidke
  • 2,787
  • 2
  • 34
  • 58
  • perhaps this link can help you https://stackoverflow.com/questions/31057/how-to-insert-a-line-break-in-a-sql-server-varchar-nvarchar-string – i3lai3la Dec 05 '17 at 06:12
  • in the select query, you will not find the result, if you loop on the result and you use `print` , you will find that the newline is already there, now depends on how you are consuming the result for example, rendering in html or text or a `usercontrol` like `datagrid`. – Monah Dec 05 '17 at 06:14
  • https://stackoverflow.com/a/37284582/73226 – Martin Smith Dec 05 '17 at 07:39

1 Answers1

1

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++).

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360