Bind Pivot table for Gridview

I am developing a roster application (asp.net with VB + sql server) to let user input shift duty record, proposed screen is as follows:

sample screen: http://i32开发者_StackOverflow社区6.photobucket.com/albums/k421/joeyan829/asp/SMW-1-1.jpg

for the database design, each staff will have one record per day.

Column name:

staff_key, shift_date, start_time, end_time, shift_patten, SL, VL, ML, PH, APH, etc.

sample data:

123, 23-5-2011, 9:00, 17:00, A, N, N, N, N, N

123, 24-5-2011, 12:00, 19:00, B, N, N, N, N, N

123, 25-5-2011, 12:00, 19:00, B, N, N, N, N, N

I know I need to use pivot table and then bind it to gridview,

example

(Peter, COII) 23/5 in column 1, (Peter, COII) 24/5 in column 2..... for row 1

sql server statement:

select troster.staff_key, shift_date, start_time, end_time, 
shift_type, SL, VL, ML, PH, APH 
from
troster 
right join 
hris_leave.dbo.tstaff 
on 
hris_leave.dbo.tstaff.staff_key=troster.staff_key
where 
troster.shift_date 
in 
('5/23/2011', '5/24/2011', '5/25/2011', '5/26/2011', 
'5/27/2011', '5/28/2011', '5/29/2011')

But I still have no idea how to amend/write the above SQL for it to become a pivot table and function to bind it to the gridview.

I don't think you should aim for a direct mapping between a database query and the grid. Instead, build the grid cell by cell, based on a query resultset filtered by the week.

It is possible to do this with ASP.NET GridView, but it is not by any means a clean solution. I would use a scheduler control, and if not available, build one from HTML elements and simple asp.net controls.

With Gridview, the basic strucure is a nine-column grid, where column header labels for the dates are calculated based on the selected week. The GridView datasource would have one row per staff member, with all the data needed (staff_key, name), and perhaps the date keys for the date columns.

In the RowDataBound event, you would use the (staff_key, date_key) pair to find the cell data. In-memory queries such as LINQ to objects or ADO.NET data strucure queries should be fine for this, but you should minimize the number of database queries and load the weekly data once.

Having editable cells makes managing the grid more complex, since you would need TemplateFields in the date columns, with an ItemTemplate and EditItemTemplate.