Export Select Query To Delimited File

The following code  will output the results of the query  (select * from  logic..temp1) to a CSV file

Please be aware that the output path will be to the sql machine if you use c:\.  Use \\servername\folder\filename.extention  to output the file to another machine.




declare @outputpath varchar(800) ----------Varchar(max) is not allowed
declare @sql varchar(8000)


set @outputpath = '\\SERVER\Shared_Drive\nick.csv'   --------- Set the Path


select @sql = 'bcp "select * from  logic..temp1"  queryout ' + @outputpath + ' -c -t, -T -S'  + @@servername

print @sql

exec master..xp_cmdshell @sql



the file will then appear in the appropriate folder.
To change the delimiter change the yellow Highlighted Character. for a pipe you will have to replace it with ^|  so the line looks like this ' -c -t^| -T -S'

Create a Delimited List in TSQL

The Following code uses Stuff to generate a delimited list from a postcode table using TSQL

SELECT STUFF((SELECT ','+ char(39) + [postcode]+char(39)
 FROM tblPostcodeRoute  where carrier = 16
 ORDER BY [postcode]
 FOR XML PATH('')), 1, 1, '') AS [Output]

Result

'HS1','HS2','HS3','HS4','HS5','HS6','HS7','HS8','HS9','IV10','IV11','IV14','IV15','IV16','IV17','IV18','IV19','IV20','IV21','IV22','IV23','IV24','IV25','IV26','IV27','IV28','IV4','IV40','IV41','IV42','IV43','IV44','IV45','IV46','IV47','IV48','IV49','IV5','IV51','IV52','IV53','IV54','IV6','IV63','IV7','IV8','IV9','KA27','KA28','KA29','KW1','KW10','KW11','KW12','KW13','KW14','KW15','KW16','KW17','KW2','KW3','KW4','KW5','KW6','KW7','KW8','KW9','PA20','PA23','PA24','PA25','PA26','PA27','PA28','PA29','PA31','PA32','PA33','PA34','PA37','PA38','PA41','PA42','PA43','PA44','PA45','PA46','PA47','PA48','PA49','PA60','PA61','PA62','PA63','PA64','PA65','PA66','PA67','PA68','PA69','PA70','PA71','PA72','PA73','PA74','PA75','PA76','PA77','PA78','PA80','PH20','PH21','PH22','PH23','PH24','PH25','PH26','PH30','PH31','PH32','PH33','PH34','PH35','PH36','PH37','PH38','PH39','PH40','PH41','PH42','PH43','PH44','PH49','PH50','ZE1','ZE2','ZE3'

Convert Column results to delimited string in TSQL

Use the below to convert the results of a query from this
123
125
156
254
214
To
123,125,156,254,214



DECLARE @result varchar(1000)

SET @result = ''

SELECT @result = @result + cast(order_id as varchar) + ',' FROM tblorders WHERE tblstatus = 2

select substring(@result, 0, len(@result))

This has worked for me no problems you can change the delimiter by changing the ' ,'



Original code that this was based on can be  found here