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-physics,chemistry;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 rollno,name,marks 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(physics,chemistry)? I searched many websites,but, 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.
Loading
Sanjeeb LenkaPosted Aug 23, 2013, 12:29 AM
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 HussainPosted Aug 23, 2013, 12:07 AM
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