Grouping data from a table

Considering that your SGBD is SQL Server you can collect the data in subquery and perform PIVOT on top of these data:

SELECT a.alunonome AS aluno,
       m.materianome AS disciplina,
       y.bim1,
       y.bim2,
       y.bim3,
       y.bim4,
  FROM (
    SELECT n.materiaid,
           n.alunoid,
           'bim' + CAST(n.notabimestre AS VARCHAR) as semestre,
           n.nota
      FROM notas n
  ) x
  PIVOT (AVG(x.nota) FOR semestre IN (bim1, bim2, bim3, bim4)) y
  INNER JOIN materias m ON m.materiaid = y.materiaid
  INNER JOIN alunos a ON a.alunoid = y.alunoid

If you are using MySQL , you can group using CASE and setting GROUP BY :

SELECT a.alunonome AS aluno,
       m.materianome AS disciplina,
       AVG(CASE n.notabimestre
             WHEN 1 THEN n.nota
           END) AS bim1,
       AVG(CASE n.notabimestre
             WHEN 2 THEN n.nota
           END) AS bim2,
       AVG(CASE n.notabimestre
             WHEN 3 THEN n.nota
           END) AS bim3,
       AVG(CASE n.notabimestre
             WHEN 4 THEN n.nota
           END) AS bim4
  FROM nota n
 INNER JOIN materias m ON m.materiaid = n.materiaid
 INNER JOIN alunos a ON a.alunoid = n.alunoid
 GROUP BY n.materiaid, n.alunoid, a.alunonome, m.materianome

  

AVG

     

Returns the average value of expr ... If there are no matching rows, AVG () returns NULL.

Free translation:

  

Returns the average value of the expression ... If there are no rows, AVG () will return NULL.

Note: I noticed that you used the MAX function to get the notes, but I think the ideal thing for you is to use AVG , since what matters is the average

Your SELECT can not start in the Notes table, it has to start in students, and make a SUB-SELECT for each bimester, more or less like this:

 SELECT b.MateriaNome, a.AlunoNome, a.AlunoID,
        (SELECT MAX(Nota) FROM Notas WHERE NotaBimestre = 1 AND MateriaId = b.MateriaId AND AlunoId = a.AlunoId) AS '1bim',
        (SELECT MAX(Nota) FROM Notas WHERE NotaBimestre = 2 AND MateriaId = b.MateriaId AND AlunoId = a.AlunoId) AS '2bim',
        (SELECT MAX(Nota) FROM Notas WHERE NotaBimestre = 3 AND MateriaId = b.MateriaId AND AlunoId = a.AlunoId) AS '3bim',
        (SELECT MAX(Nota) FROM Notas WHERE NotaBimestre = 4 AND MateriaId = b.MateriaId AND AlunoId = a.AlunoId) AS '4bim',
    FROM Alunos a
    INNER JOIN Materias b
    GROUP BY a.AlunoID, b.MateriaId;