how to dynamically create multiple select statement and store it in a datatable

Question

the case here is the number of columns i would like to store to the datatable depends on the number of available data. i would need a multiple select statements to collect all the data.

select test_result.stud_id,test_info.max_score,
test_result.test_score 
from test_result left join test_info 
  on test_result.test_info_id = test_info.test_info_id 
where test_info.test_type_id = 1 and test_info.test_num = 1;

i would have to repeat this code over and over again until the test_num reaches the current maximum count. i was thinking of looping this code while storing it to the datatable.this is what i would like the datagridview would display.

|Quiz Number  | Quiz#1 | Quiz #2 | Quiz #3 |  Quiz #4 |  Quiz #5 |
|Max score    |    20  |    25   |    30   |    15    |    15    |
|student 1    |    18  |    22   |    25   |    12    |    14    |
|student 2    |    19  |    20   |    25   |    11    |    13    |
|student 3    |    20  |    24   |    20   |    12    |    11    |

the display of data would be in rows. so each row would require a different select statement in order to display the required data.

im using mysql for my database and im using c# as my front end — Deviruchi D Devourer
– Deviruchi D Devourer, Commented Dec 19, 2013 at 16:07

Madhivanan · Accepted Answer · 2013-12-20 09:38:26Z

1

Something like below will woek for you

select 
test_result.stud_id,
max(case when test_result.test_info_id =1 then test_result.test_score end) as quiz_1,
max(case when test_result.test_info_id =2 then test_result.test_score end) as quiz_2,
max(case when test_result.test_info_id =3 then test_result.test_score end) as quiz_3,
.
.
. 
from test_result left join test_info 
  on test_result.test_info_id = test_info.test_info_id 
where test_info.test_type_id = 1 and test_info.test_num = 1
group by test_result.stud_id;

answered Dec 20, 2013 at 9:38

Madhivanan

13.7k1 gold badge28 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Deviruchi D Devourer Over a year ago

thanks man it worked. but i was wondering if how can i make the cases dynamic or is it even possible? coz the test_info_id are all dependent on my selected combo box items

Collectives™ on Stack Overflow

how to dynamically create multiple select statement and store it in a datatable

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related