Please excuse me if a similar question has been answered elsewhere, but I haven't found it at this point.
I'm trying to format the results of a SQL Server query (via sqlcmd) so that the output is a CSV file with the column header first, followed by the value. The query will only ever return one record's values.
As an example, here's a record I could expect to pull from the table:
InputFileName InputFileSizeBytes JobId Steps NumSteps
------------- ------------------ ----- ----- --------
TestData.dat 1024 32768 1 2
With this data, I would want to generate a CSV file that looks like this:
InputFileName,TestData.dat
InputFileSizeBytes,1024
JobId,32768
Steps,1
NumSteps,2
So far, nothing I've come up with has worked. For instance, I attempted to UNION the values with constant strings, as well as give them an order:
SET NOCOUNT ON;
SELECT 'InputFileName' AS InputFileName,
'InputFileSizeBytes' AS InputFileSizeBytes,
'JobId' AS JobId,
'Steps' AS Steps,
'NumSteps' AS NumSteps,
'ClientEmailMessage' AS ClientEmailMessage,
'ClientContactInfo' AS ClientContactInfo,
'DTInQueueUTC' AS DTInQueueUTC,
1 AS ordering
FROM HS_JOB_QUEUE
UNION
SELECT InputFileName,
InputFileSizeBytes,
JobId,
Steps,
NumSteps,
ClientEmailMessage,
ClientContactInfo,
DTInQueueUTC,
2 AS ordering
FROM HS_JOB_QUEUE
WHERE JobId = 79000
ORDER BY ordering;
This did not work at all - sqlcmd spat out an error saying the constants could not be used in a UNION operation.
Do you have any suggestions on how I can get this to work?
