insert headers into sql query result

Question

Post reply

insert headers into sql query result

mbhout22

SSC-Addicted

Points: 404
More actions
September 17, 2009 at 9:58 am

#93951

Hi ,

I am new to TSql and need to add dummy headers to my sql result before i export it to a csv format.

i can have a header which can be a noddy as '1' or 'header' as the macro that need to read my result start reading data from the 3rd row

Many thanks,

Christian

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Garadin One Orange Chip Points: 29613 More actions · Answer 1

First, a disclaimer.

This is most likely not the best method for this. There may be an exporting service like SSIS or one of those that can handle this much more eloquently. Unfortunately, I don't deal with those and can't tell you which you can use.

*A* way to do this using TSQL would be:

Example Data set:

Date User Sales Earnings

9/1/09 Mike 15 500.00

9/2/09 Sally 12 400.00

9/5/09 Greg 52 4000.00

Say this query generates the dataset normally:

SELECT Date, User, Sales, Earnings

FROM MagicalReportReadyTable

This is how you could do it to include column headers in the result set:



SELECT CAST('Date' as varchar(25)) Date,

           CAST('User' as varchar(50)) User,

           CAST('Sales' as varchar(6)) Sales,

           CAST('Earnings' as varchar(12)) Earnings

UNION ALL

SELECT CAST(Date as varchar(25)),

           CAST(User as varchar(50)),

           CAST(Sales as varchar(6)),

           CAST(Earnings as varchar(12))

FROM MagicalReportReadyTable

You need to cast the values because your column headers are all char types and your data is likely not. I'm casting the initial ones because UNION ALL takes the datatypes from the first resultset, and unless I cast them, you may not have enough character width with the column headings. The field Aliases are extraneous given what you're using this for, but I like using them anyways. Don't mind my rampant usage of keywords as column names, this was just a quick example 🙂

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

roger young-372270 SSC Veteran Points: 232 More actions · Answer 2

There are several ways to do this but it really depends on your preferences.

You can use the union method above.

If you plan on executing this query from SSMS everytime; you could set the query options to place a header on your result set. (query -> query options; select grid on left pane and check "include column headers when copying or saving the result". Then when you get the result set you can right click and save.

You could also create an SSIS package and set the file properties to include the header. This gives you an option of running this as a job, then it's a set it up once and schedule it to run.

Another option is to embed the query in excel, then you refresh the worksheet and excel automatically puts the headers on. Then do a save as *.csv. This allows you the option of handing it off to end user to refresh at their leisure.

There's probably many more methods, but it really comes down to where you do most of your work, what you are most comfortable with, and how many of these you will be doing.