Display values in gridview based on value of another table

Question

i have two tables: subject table: dept | sem | subject EC | 4 | physics EC | 4 | chemistry EC | 5 | english marks table: dept | sem | rollno |name | subject | marks EC | 4 | 1 | abc | physics | 80 EC | 4 | 2 | xyz | physics | 95 EC | 4 | 1 | abc | chemistry| 90 EC | 4 | 2 | xyz | chemistry| 85 my expected output in the gridview is like: (the subject names are retrieved from the subject table based on the sem values(sem 4-physicschemistry;sem 5- english and so..) dept| sem | rollno |name | physics | chemistry | total EC | 4 | 1 | abc | 80 | 90 | 170 EC | 4 | 2 | xyz | 95 | 85 | 170 when i use a query like: select rollnonamemarks as physics from marks where subject='physics' i get it perfectly as my expecatation.but i want to display the output in a gridview in asp.net webpage using c# .so how can do this for multiple column values(physicschemistry)? I searched many websitesbut still im not having any clear idea.should i acheive my goal using SQL QUERY OR GRIDVIEW FEATURES? if so what features and how? pls help.

Sanjeeb Lenka · Answer

Refer to this link http://www.dotnetfunda.com/articles/article1400-how-to-generate-gridview-columns-dynamically-based-on-user-selection.aspx and you have to use pivot table http://vivekjohari.blogspot.in/2012/01/pivot-table-in-sql-server.html

Iftikar Hussain · Answer

Hi You first need to convert your rows value to column. Since the subject are not fixed there for you need to write string sql. Please refer the below link http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server-2008 once you create the stored procedure with your expected result then you can call your stored procedure in your code and display the data in gridview Regards Iftikar