0

I have three MySql tables:

Students - StudentId, StudentName

Assessments - AssessmentId, AssessmentName

Results - StudentId, AssessmentId, Grade

I can't predetermine the number of students or assessments, as these are created by users.

How can I select a two-way table, showing the results of all students' assessments (which will have an unpredetermined number of rows and columns):

|--------|---------|---------|---------|---------|---------|
|User    |Test A   |Test B   |Test C   |Test D   |Test E   |
|--------|---------|---------|---------|---------|---------|
|Alex    |A        |A        |C        |Null     |F        |
|Ben     |Null     |D        |A        |Null     |Null     |
|--------|---------|---------|---------|---------|---------|

I am looking to do this either in a SQL select, or using LINQ to Entities in ASP.NET, if that has an efficient method for achieving the same.

Thank you.

James
  • 7,343
  • 9
  • 46
  • 82

2 Answers2

0

You cannot achieve this in a single SQL query. You will need a function that will generate a result-set with variable columns and then make a select from it.

Andriy M
  • 76,112
  • 17
  • 94
  • 154
CristiC
  • 22,068
  • 12
  • 57
  • 89
  • Thanks. Is this possible within a MySql command? Or do I need to make several database calls? – James May 18 '11 at 14:56
  • You will need 1 select to return the number of cols (Tests) and another to make a cursor from it. – CristiC May 18 '11 at 16:46
  • Could you give an example please? – James May 18 '11 at 17:23
  • SELECT id from Assessments - the first query. After this you can create a temp table having assessments as columns. And then with a simple query like SELECT StudentName, AssessmentId, Grade FROM Results, Students; you can populate the temporary table. In my opinion, this will not be to easy to accomplish, but is doable. – CristiC May 19 '11 at 12:02
0

I don't think you can create a pivot or crosstab directly in MySQL. You could select the results ordered by user, test then rearrange them into a cross tab. See e.g.

Is it possible to Pivot data using LINQ?

Linq: How to transform rows to columns with a count (Crosstab data)?

Community
  • 1
  • 1
JB.
  • 101
  • 4