Preceding data with rows containing literal values

1

I am working on a query to produce a CSV file for importing into a payroll system and the file requires fixed literal values in the first three rows.

The first 3 rows should look as follows ( | indicates where column begins / ends) ....

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

... then the data follows as below...
001 | 3.5 | 8.5
002 | 8 | 12
003 | 10 | 3

I tried a union but realised that the data types needed to match in each section. Of course this was not the case due to the hours being REAL data types.

Here is an sample of the type of data I am dealing with. Any help would be appreciated.

DECLARE @MyTable TABLE (EmployeeID VARCHAR(10), Hours1 REAL, Hours2 REAL)

INSERT INTO @MyTable
SELECT
'001', 3.5, 8.5
UNION ALL
SELECT
'002', 8, 12
UNION ALL
SELECT
'003', 10, 3

2

Not exactly sure what you are asking. Are you trying to combine character data with a real? If so convert the real to character.

3

Hi, it's quite difficult for me to explain and it didn't help that i had neglected to mention that i am using a pivot. Here is a more accurate example of the data.

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

If you execute this query and then imagine the two rows in the result set preceded by the 3 rows below ...

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

... this is what i wish to achieve. Is this any clearer?