Following are my tables
PartyMaster - PId PK Identity | PName varchar
ItemMaster - ItemId PK identity | ItemCode varchar unique | ItemName varchar
ItemVendorMaster - ItemVendorId PK Identity | ItemId FK(ItemMaster-ItemId) | PartyId FK(PartyMaster-PId) | Percent double
Now I want to show the above 'ItemVendorMaster' details in gridview asp.net as follows
ItemId | ItemCode | ItemName | Party1 | %1 | Party2 | %2 | Party3 | %3
1 | 1 | PC | ddl(P1)|50 | ddl(P2)| 50 | ddl(NS) |
2 | 2 | CPU | ddl(P2)|70 | ddl(P4)|20 |ddl(P8) |10
ddl - Drop Down List
NS - Not Selected(Blank)
But the data in table 'ItemVendorMaster' is like as follow
ItemVendorId | ItemId | PartyId | Percent
1 | 1 | 1 | 50
2 | 1 | 2 | 50
3 | 2 | 2 | 70
4 | 2 | 4 | 20
5 | 2 | 8 | 10
How can I show above grid, I am not getting sql query for it, Please Help
Note:
In grid same row must not have same name selected from dropdown
Sum of all % in same row in grid must be 100%