0

I've a query which I need to convert to CSV, but to return the CSV as a query result, not save to/as a file.

All I can find are examples on how to "save" a queries' output as a CSV (Export table from database to csv file), but I need to have the CSV as an output of a query so it can be returned like a normal query.

Is there a more elegant/efficient method other than doing...

SELECT  STUFF(( 
SELECT  ',' + CSV
FROM( 
    SELECT  
    CONVERT(VARCHAR,Stat_Date,103) + ',' 
    + COALESCE(UPRN, '') AS CSV
FROM [stats].[tvfSomeFunction](@TargetDate) 
) x
FOR
XML PATH('')), 1, 1, '') CSV

As theres in addition to lots of rows, also lots of columns, so this method is tedious to create/maintain.

Markus
  • 20,838
  • 4
  • 31
  • 55
Vereonix
  • 1,341
  • 5
  • 27
  • 54
  • 4
    This [is possible](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=bc35cbca47de0a3a1fcdabafe49e322b) using [`CONCAT_WS()`](https://learn.microsoft.com/en-us/sql/t-sql/functions/concat-ws-transact-sql?view=sql-server-ver16) and [`STRING_AGG()`](https://learn.microsoft.com/en-us/sql/t-sql/functions/concat-ws-transact-sql?view=sql-server-ver16). But in the same way it is possible to drive a screw into wood with a hammer... It can be done but a hammer is not the right tool for the job. I'd use CSV or since you've tagged c# use a DataReader and StreamWriter to create your file – GarethD Jul 13 '22 at 12:38
  • Read ALL the answers provided in your linked question. The one posted by [svenGUTT](https://stackoverflow.com/users/3084699/svengutt) is what you need. IOW You write SQL to write the statement that you actually want to execute. – SMor Jul 13 '22 at 12:38
  • Use a tool such as SSIS or `bcp`, or even Excel can do this. T-SQL is not the place for this type of code, it's designed for selecting a normal resultset. – Charlieface Jul 13 '22 at 12:40
  • @GarethD I'm actually currently turning the result into a CSV in C#, but the looping through each row to generate the CSV is taking too long so looking at moving to SQL side. The c# speed issue might be that I'm only testing locally atm and my work laptop aint great. – Vereonix Jul 13 '22 at 12:41
  • @Charlieface I agree, but I'm having to create a data download for our website and the limitations I have are it needs to be called via our API, and the API can only communicate to the DB/get data via SPs :/ – Vereonix Jul 13 '22 at 12:44
  • 2
    I highly doubt moving this to the SQL side won't be any faster. It may be worth posting the c# code you are using as your problems might lie with your actual c# code, rather than the approach as a whole – GarethD Jul 13 '22 at 12:45
  • *N.B in my original comment I meant "I'd use SSIS" and not "I'd use CSV" - the latter makes no sense at all.....Although with the further requirements now posted it seems SSIS is not an option either* – GarethD Jul 13 '22 at 12:47
  • 1
    I agree, I suggest you fix the C# code, rather than trying to shoehorn this into T-SQL. You can stream it, which should help performance – Charlieface Jul 13 '22 at 12:57

0 Answers0