You may have a really good reason for wanting to have a table with this structure, but, in my opinion, it is a very bad idea. You are de-normalizing the data and that will lead to very difficult queries on this table. And what happens when FY6, FY7 and FY8 need to be added? Maintenance becomes an issue.

I think you would be better off with a table that looks something like:

CREATE TABLE Combined

(

SysID int identity(1,1) not null, --not strictly needed, but helps to make the row unique (personal preference)

FY int not null,

CustomerNo int not null,

OrderAvg int,

OrderCount int

)

With this table, you can add as many, and I am guessing here, Fiscal Years as necessary. Then you can query on specific FY's. You don't have to worry about NULL either. If the FY does not exist for a CutomerNo, then there is nothing!

So your query to load this table would look something like this:

INSERT Combined (FY, CustomerNo, OrderAvg, OrderCount)

SELECT 1, CustomerNo, OrderAvg, OrderCount

FROM FY1

INSERT Combined (FY, CustomerNo, OrderAvg, OrderCount)

SELECT 2, CustomerNo, OrderAvg, OrderCount

FROM FY2

etc . . .

Then you can add any necessary indexes/keys.

If you then need to put the data into columnar form, de-normalize it, you can use PIVOT to achieve that. But other queries will be MUCH simpler.