1

I would like to pivot rows to columns but provide a grade for every grade-sweep (1-4), even if student has no grade in that subject...

Here are my tables

students             subjects               gradesweeps
id  | stuname        id  |  subname         id  |  gradesweep
1   | steve          1   |  english         1   |  check_1
2   |  Bob           2   |  maths           2   |  check_2
                                            3   |  check_3
                                            4   |  check_4


grades
id | student_id | subject_id | gradesweep_id | grade
1    1            1            1               4
2    1            1            3               6
3    1            1            4               5

Student 1 has 3 progress checks out of 4 in English but would like to be able to see grade-sweep 2 as an empty result or Null...

Is it possible to have a resultset like this;

student_id = 1
subject_id = 1

student_id | subject_id | Check_1 | Check_2 | Check_3 | Check_4
1            1            4                   6         5

Or even better;

student_id | subject_id | gradesweep_id | grade
1            1            1               4
1            1            2               null or blank
1            1            3               6
1            1            4               5
adam
  • 39
  • 2

1 Answers1

0

Designing a database to hold empty columns are not recommended, and should be avoided. If you are having trouble designing your database, I recommend reading up on database normalization. The following link explains (1NF), and goes on to (2NF) and (3NF).

https://www.essentialsql.com/get-ready-to-learn-sql-8-database-first-normal-form-explained-in-simple-english/

Happy coding!

Tomse
  • 179
  • 5