Dynamic field names

[IShouldHaveKnownBetter]

I'm a real newbie at this...

I've got a table of invoices and have created a ms sql query giving me a total of the invoices by client across months. So my columns are Client, Month 0, Month -1, Month -2, etc. (where Month 0 is the current month, month -1 is last month, and Month -2 is the month before that). Rather than column names like "Month - 1" I want to show the actual year and month as the column heading (like 2008-07, 2008-06, 2008-05, etc.). Can this be done? If so, how?

Thanks.

[Delerna]

There are two ways that I can think of.
1) Do it in the frontend, where you are displaying the results of the query.
You should be able to write some code to set the column names.

2)
To do it within the query itself you will have to write a dynamic query and for that you will need to put the query into a stored procedure.


another possibility would be to put the yearmonth into the recordset itself
by using a union query and ensure the yearmonth row is at the top.
Maybe the yearmonth values could be stored in a table that is generated at the start of each day via DTS and the SQL Agent scheduler.
Or a UDF could generate them on the fly.