Column Names on the fly 1

[Jjinks]

I wish to add a new column each month and store data.

Explanation of what I would like to do:

I have an existing table with the following columns:

CustomerNumber
Jan-02
Feb-02
.
.
.
Dec-03

I now have the data for Jan-04 (9000 rows) which shows customers who purchased in Jan-04, I wish to add Jan-04 data. Some of Jan-04 Customers will not exist in the table others will and there status will be updated using the column Dec-03 (62000 rows) as the base data.

Hope this explains better what I am trying to achieve.

Thanks,

Ian

 

[nigelrivett]

Don't do it.
Data structures should be static - ignoring the fact that eventually you will come up against the limit of columns in a table.

Instead hold the data with month and value columns then adding new months is a matter of adding data rather than columns. Create a month table to reference via a forreign key if you wish.
For reporting perform a cross tab.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[Jjinks]

Nigel,

Thanks for the reply. That is the approach I have taken now.

Ian