SQLTeam.com | Weblogs | Forums

Preceding data with literal values


#1

Firstly, apologies if anyone has already read this in a previous entry but I am still wondering how best to tackle this. Here we go....

Basically, I have a query similar to the one below that will be used as a basis for generating a CSV file but i need 3 rows (containing the literal values) preceding the results.

Query:

DECLARE @MyTable TABLE (EmployeeID VARCHAR(10), Hours REAL, HoursType VARCHAR)

INSERT INTO @MyTable
SELECT
'001', 3.5, 'A'
UNION ALL
SELECT
'001', 8, 'B'
UNION ALL
SELECT
'002', 10, 'A'

SELECT *
FROM
@MyTable PIVOT (SUM(Hours) FOR HoursType IN (A,B) ) AS p

Literal values:

< DEFN > |
< EMPID > | < COSTCEN >
< DATA > |

So, in summary i need the result set to look something like this....

< DEFN > |
< EMPID > | < COSTCEN >
< DATA > |
001 | 3.5 | 8
002 |10 | NULL


#2

Regardless of the tool/method you are using to export, you can first write the 3 header rows into your file, and then append the results of the query.

Alternatively, create a file just with the headers (call this header.csv), export your data to a temorary csv file ( call it data.csv) and then use a shell command to append the two files together like this to your final output file, Final.csv:

copy /B header.csv + data.csv Final.csv

#3

Hi James,
I'll give that a go. I wasn't thinking outside of the box... my mind was telling me the rows had to be inserted through the query.
Thanks for your advice!!