MySQL query assistance

Question

Here is a description of the table i am using:

describe mjla_db.StudentRecordTable2;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| classId   | varchar(20) | NO   | MUL | NULL    |       |
| studentId | varchar(20) | NO   | MUL | NULL    |       |
| quizGrade | tinyint(4)  | YES  |     | NULL    |       |
| quizId    | int(11)     | NO   | MUL | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

Here is example data in the database:

+------------+-----------+------------+---------+------------+
| Student ID | Last Name | First Name | Quiz ID | Quiz Grade |
+------------+-----------+------------+---------+------------+
| A1         | Cat       | Tom        |      19 |       75   |
| A2         | pancake   | Harry      |      19 |       65   |
| A5         | Worthy    | Dick       |      19 |       NULL |
| A1         | Cat       | Tom        |      20 |       55   |
| A2         | pancake   | Harry      |      21 |       NULL |
| A2         | pancake   | Harry      |      20 |       47   |
| A5         | Worthy    | Dick       |      20 |       95   |
| A1         | Cat       | Tom        |      21 |       55   |
| A5         | Worthy    | Dick       |      21 |       95   |
+------------+-----------+------------+---------+------------+
3 rows in set (0.00 sec)

The result i am trying to get is one that will look similar to the following:

+------------+-----------+------------+---------+------------+------------+
| Student ID | Last Name | First Name | Quiz 19 | Quiz 20    | Quiz 21    |
+------------+-----------+------------+---------+------------+------------+
| A1         | Cat       | Tom        |      75 |       55   |       55   |
| A2         | pancake   | Harry      |      65 |       47   |       NULL |
| A5         | Worthy    | Dick       |     NULL|       95   |       95   |
+------------+-----------+------------+---------+------------+------------+

Where the Student ID column is unique.
Where the quiz columns continue depending on how many quizzes are in the original table. And the quiz columns contain the grade of each of the respective students.

FYI - The technical term for what you are trying to do is called "pivot tables" — ChrisLively
– ChrisLively, Commented Oct 23, 2011 at 1:43
@ChrisLively: Wow, thanks! Found some great examples. I did not know the correct term. What i was trying was WAY off... =( — prolink007
– prolink007, Commented Oct 23, 2011 at 1:57
The text you keep adding provides no value to your question. Funny that you keep re-adding it instead of demonstrating you've actually tried to figure this out yourself... — OMG Ponies
– OMG Ponies, Commented Oct 23, 2011 at 2:22

Mahmoud Gamal · Accepted Answer · 2011-11-23 09:00:09Z

3

try this:

 select s.StudentId, s.FirstName, s.LastName, 
 Case when s.QuizId = 19 then quizGrade end as 'Quiz 19',
 Case when s.QuizId = 20 then quizGrade end as 'Quiz 20',
 Case when s.QuizId = 21 then quizGrade end as 'Quiz 21'
 from StudentRecordTable2 sr 
 inner join Students s on sr.StudentId = s.StudentId

see this

edited Nov 23, 2011 at 9:00

answered Oct 23, 2011 at 1:55

Mahmoud Gamal

80.2k18 gold badges143 silver badges168 bronze badges

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

Collectives™ on Stack Overflow

MySQL query assistance

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related