asp.net - Group and update multiple records into one gridview row -

i need enhance application , table structure cannot amend.

table structure:

id      staffid     type ======================== 1       1           1 2       1           2    3       1           3 4       2           2 5       2           3 6       3           1 

i want group same staffid 1 gridview row , databind checkbox (type 1, type 2, type3) based on value of "type" column.

proposed gridview :

staffid     staffname   type 1  type 2  type 3 ============================================== 1           amy         [x]     [x]     [x] 2           john        [ ]     [x]     [x] 3           chris       [x]     [ ]     [ ] 

moreover, how can insert/delete record if user check/uncheck checkbox in gridview ? thanks.

this looks me more of sql question answer accordingly. if aren't looking sql-based answer please more clear in question. , if please amend tags.

as long number of types fixed , known, following sql can form underlying sql query (assuming source sql database):

select a.staffid, name,      case when b.id not null 1 else 0 end type1,     case when c.id not null 1 else 0 end type2,     case when d.id not null 1 else 0 end type3 dbo.staff left join dbo.stafftype b on a.staffid = b.staffid , b.typeid = 1 left join dbo.stafftype c on a.staffid = c.staffid , c.typeid = 2 left join dbo.stafftype d on a.staffid = d.staffid , d.typeid = 3 

in gridview each checkbox know staffid (from row) , typeid (from column) handle onchanged event , call like:

declare @staffid int = 1, @typeid int = 1  if exists (select * dbo.stafftype staffid = @staffid , typeid = @typeid)     delete dbo.stafftype staffid = @staffid , typeid = @typeid else     insert dbo.stafftype ( staffid, typeid )     values ( @staffid, @typeid ) 

updated linq version:

var q2 =     staff in stafflist     type1 in typeslist         .where(t => t.staffid == staff.staffid && t.typeid == 1)         .defaultifempty()     type2 in typeslist         .where(t => t.staffid == staff.staffid && t.typeid == 2)         .defaultifempty()     type3 in typeslist         .where(t => t.staffid == staff.staffid && t.typeid == 3)         .defaultifempty()     select new     {         staffid = staff.staffid,         name = staff.name,         type1 = (type1 != null),         type2 = (type2 != null),         type3 = (type3 != null)     };