Dynamic Column names

[coolCoder]

Hi All,
I have a tricky situation here. I want that the column
headers of a query result should contain some dynamic information ( ex,
date ).

If my query output is something like this --

SrNo Column1 Column2

1 ABC 12
2 PQR 6
3 XYZ 23


I want to append suppose getdate() in the column headers. Like
this --

SrNo Column1 On 10/16/2006 Column2

1 ABC 12
2 PQR 6
3 XYZ 23


Note that Column1 is changed to Column1 On 10/16/2006. This
appended date will be dynamic. It will be stored in a variable. For
your information this query will be a part of a stored procedure.

Can you suggest a way for the above problem ?

 

[Cowboy \(Gregory A. Beamer\)]

You can change the column on the UI quite easily by changing the grid view
header for the column. This will require turning off autobind, of course.

To do it in SQL, you will have to do something like this:

1. get the current date
2. Set up a create table string that mimics the query, including header
names (temp table?) - sp_execsql
3. insert rows that match the query in the table from #2
4. Select out

I know no way of creating a dynamic alias other than a temporary container,
like above. For the display, I would aim for altering the UI rather than
setting the column name in the query, but you may have other reasons for
needing it this way.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com

*************************************************
Think outside of the box!
*************************************************

 

[coolCoder]

I know no way of creating a dynamic alias other than a temporary container,
like above. For the display, I would aim for altering the UI rather than
setting the column name in the query, but you may have other reasons for
needing it this way.

Hi,
Thanks for your reply ! It should work ( i have not tried it yet
). As far as the reasons, for not altering this in the UI is that i am
using the stored procedure to generate a report. i have made such a
program that whatever output the stored procedure will return, the
program will format the results and send a mail for the same. Now this
program is in production and i will have to request a down time for
changing the program. Instead i thought if it would be possible for
altering the stored procedure will always be good. I actually thought
of the solution before but wanted to know whether there is any built-in
way of doing it.
Any ways thanks again for replying and I think i will go with the
solution.


Thanks !