How can I get a SQL query result in CSV format?

Question

I have an SQL query that returns some rows. How can I change it so that the output of the query is in CSV format?

I have found a way to make the result in XML format, but because of the nature of XML the result is too large to be useful.

The query is very large (and it's not really important), so I wont post it here. Here is a link to it.Link

I don't want the SQL query to save the result as a CSV file.

Question, is this a one time query or will you need to run this multiple times? — CPearson
– CPearson, Commented Jul 21, 2017 at 20:52

Valentin Petkov · Accepted Answer · 2017-07-21 14:44:23Z

1

YES

try this

DECLARE  @query nvarchar(max)

select @query = STUFF(  (SELECT N',' + cast( id as nvarchar)  
from TableX 
--some WHERE clause here as well

FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)')  ,1,1,'')

select  @query

NO variable :

 select   STUFF(  (SELECT N',' + cast( id as nvarchar)  
 from TableX 
 FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)')  ,1,1,'')

edited Jul 21, 2017 at 14:44

answered Jul 21, 2017 at 14:26

Valentin Petkov

1,66619 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

CPearson Over a year ago

This is not a complete solution. You only have this set up to work for id field. If you look at the link provided @user3465882 has some fields that could have commas and double quotes. Also, you don't include the headers for the columns. See en.wikipedia.org/wiki/Comma-separated_values for some standard rules.

Dan Bracuk · Accepted Answer · 2017-07-21 16:39:06Z

0

Like this:

select field1
, '","'
, field2
, '","'
etc

answered Jul 21, 2017 at 16:39

Dan Bracuk

20.8k5 gold badges30 silver badges44 bronze badges

Collectives™ on Stack Overflow

How can I get a SQL query result in CSV format?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related