SQL Server line break for CSV

Question

I am trying to write a query in CSV format, it is below:

SELECT SUBSTRING
(
    (
        SELECT ',' + [symbol], + ',' + [date] + ',' + [price]
        FROM csvFormatTable
        FOR XML PATH('')
    )
    ,2,200000
) 
AS CSV

and it mostly gives the correct result apart from the ',' at the end of the line but I do want to replace it with a line break. I've seen CHAR(10) and similar stuff like that but it does not give a line break but instead a '#x0D'. The result of the query above is what I have (without the char(10) stuff) below:

symbol,date,price,mikeCode,2019-04-10,50,mikeCode,2019-04-11,200,mikeCode,2019-04-12,10,

Where as it should be:

symbol,date,price
mikeCode,2019-04-10,50
mikeCode,2019-04-11,200
mikeCode,2019-04-12,10

It needs the line break so it can be readable as a CSV.

It's the table where It's like the CSV format, exmaple: [symbol] [date] [price] 1 mikeCode 2019-04-10 50 — NoobCoder, May 10 '19 at 12:15
That doesn't makes any sense. You should really edit your question and post an example — Nick.Mc, May 10 '19 at 12:16

Thom A · Answer 1 · 2019-05-10T13:12:08.307

2

Assuming you're on SQL Server 2016- then use (N)CHAR(13) and (N)CHAR(10). You'll then need to use TYPE, to avoid the escaping of the characters, and then value to get the values out.

SELECT STUFF((SELECT ',' + CHAR(13) + CHAR(10) + [symbol] + 
                     ',' + [date] + ',' + [price]
             FROM csvFormatTable
             FOR XML PATH(''),TYPE).value('.','varchar(MAX)'),1,3,'') AS CSV;

If you are on SQL Server 2017+, you can use STRING_AGG

SELECT STRING_AGG(CHAR(13) + CHAR(10) + ',' + [symbol] + ',' + [date] + ',' + [price],CHAR(13) + CHAR(10))
FROM csvFormatTable --Untested

db<>fiddle

edited May 10 '19 at 13:12

answered May 10 '19 at 11:51

Thom A

88,727
11
45
75

Unfortunately neither are giving the line break when I paste the results into a CSV file. I was thinking this might be easier to do if I stringed the results and then tried to add the line break. – NoobCoder May 10 '19 at 12:09
https://stackoverflow.com/questions/53115490/how-to-correctly-insert-newline-in-nvarchar/53115559#53115559 – Thom A May 10 '19 at 12:31
That's because of your SSMS settings @Noobcoder , not the SQL. – Thom A May 10 '19 at 12:33
I have enabled the tick box for `Retain CR/LF on copy or Save` but the `CHAR(13) + CHAR(10)` is still showing `#x0D` – NoobCoder May 10 '19 at 12:57
@NoobCoder that is literally impossible with the code I've provided you. I'll post a DB fiddle later to confirm, but if you are getting escaped characters you've done something wrong. – Thom A May 10 '19 at 13:00
@NoobCoder DB<>Fiddle added to confirm working as you want. If this isn't working, then, like I said, you've done something wrong. You'll need to show what you've done, so we can help you correct it. – Thom A May 10 '19 at 13:12

score 0 · Answer 2 · answered May 10 '19 at 14:39

Thanks to @Larnu I changed the settings in my SMSS (link on how to do this is here): How to correctly insert newline in nvarchar

And I just changed my query around a bit to get the wanted results to

    DECLARE @SQL NVARCHAR(MAX)
    SET @SQL =
    '
        DECLARE @test NVARCHAR(MAX)
        SET @test = (SELECT STRING_AGG(CHAR(10) + [symbol] + '','' + [date] + '','' + [price], CHAR(13) ) FROM csvFormatTable)
        SELECT @test
    '

EXEC(@SQL)

SQL Server line break for CSV

2 Answers2