SSMS TSQL result to CSV with line break

RJ 106 Reputation points
2021-10-07T22:09:34.327+00:00

Hi there,

I'm trying to do a one time data export from a query to CSV and seeking your help on the below hiccup attached as image.

CREATE TABLE #temp
(
ZID int ,
City varchar(50)
)

INSERT INTO #temp VALUES (1, 'Atlanta')
,(1, 'Boston')
,(2, 'Chicago')
,(1, 'Clevland')
,(2, 'Dallas')
,(3, 'Los Angeles')
,(3, 'Seattle')

select * from #temp

SELECT DISTINCT ZID
,stuff((
SELECT ';'+'"'+t1.city+'"'
FROM #temp t1
where t1.zid = t.zid
FOR XML PATH('')),1,1,'') ConCat
from #temp t

138705-q1.jpg

138693-q2.jpg

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,334 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,597 questions
{count} votes

3 answers

Sort by: Most helpful
  1. MelissaMa-MSFT 24,186 Reputation points
    2021-10-08T03:09:36.847+00:00

    Hi @RJ ,

    Welcome to Microsoft Q&A!

    Per my knowledge and after some research, it is very difficult to achieve the result in CSV like the first snapshot.

    But you could refer to below which has the similar result and check whether it is helpful to you.

    SELECT DISTINCT ZID  
    ,stuff((  
    SELECT char(10)+' "'+t1.city+'"'  
    FROM #temp t1  
    where t1.zid = t.zid  
    FOR XML PATH('')),1,1,'') ConCat  
    from #temp t  
    

    Output:
    138734-out1.png

    138735-out2.png
    If you would like to have more double quotos like your second snapshot, you could refer to below:

    SELECT DISTINCT ZID  
    ,'"'+stuff((  
    SELECT char(10)+'""'+t1.city+'""'  
    FROM #temp t1  
    where t1.zid = t.zid  
    FOR XML PATH('')),1,1,'')+'"' ConCat  
    from #temp t  
    

    Output:
    138708-out3.png

    Best regards,
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

  2. Olaf Helper 43,166 Reputation points
    2021-10-08T05:42:13.937+00:00

    from a query to CSV

    CSV files (or better the consumer of CSV fiels ) don't support line breaks and that fact has nothing to do with SQL Server. Remove the line breaks using the SQL function REPLACE.

    0 comments No comments

  3. Tom Phillips 17,721 Reputation points
    2021-10-08T15:25:15.147+00:00

    Your problem is not with SQL Server but with Excel. CSVs do not support inline CR/LF. Your best method is to output a text delimiter like "<CR>" and then after opening in Excel, replace "<CR>" with CR/LF.

    0 comments No comments