Populate a .net gridview with transform query results

My stored procedure returns results something like this for 52 weeks.

Category | Product | wk1Amt | Wk1Qty | Wk2Amt | Wk2Qty | ...........| Wk52Amt | Wk52Qty

I am able to populate a gridview with autogeneratecolumns ="false just like above. But I need my gridview like below with editable columns in between

Category |Product |Wk1Amt |Wk1Qty | Wk1NewQty | Wk2Amt |Wk2Qty |Wk2NewQty ..........|Wk52Amt| Wk52Qty|Wk52newQty

Another thing is I need to display only the week that I have selected.
If I am running for example only Week 1 to Week 4, the columns for Week 5 to Week 52 should be invisible as they will be blank anyways.

The columns called "Wk#NewQty" are going to have textboxes so values could be entered. Do I need to create 52*2 boundfield columns manually along with 52 template columns? Or should I dynamically add columns in GridView.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Shaun KlineLead Software EngineerCommented:
Is it a business requirement to have the data displayed as a pivot table? If so, consider asking for a change. Data in that format is best displayed on reports, not for maintaining data.

If it is not a business requirement, the suggested approach is to show the information vertically.

Category | Product | Week | Amount | Quantity | New Quantity

This would provide you the flexibility you desire with less headaches when you attempt to save changes back to your data source.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
David L. HansenCEOCommented:
I concur with Shaun as to the Category, Product, Week, etc. to be used as row headers, then have the week specific data as column headings.

Whether you do it this way or not, you can fill a regular disconnected datatable with the results of your query. Then connect it as the datasource to the grid. Finally, at runtime, trap any cell value changes through event handlers (like cellValueChanged) and figure out which cell was altered so you can manually run an Update statement to the database. This, of course, will be based on the user provided value entered in the cell.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.