Concatenate data from a table

Asked

Viewed 33 times

0

I have the following sql statement: 

SELECT a.alunonome AS aluno, 
           m.materianome AS disciplina, 
           AVG(CASE n.notabimestre
                 WHEN 1 THEN n.nota
                 ELSE null
               END) AS bim1,
           AVG(CASE n.notabimestre
                 WHEN 2 THEN n.nota
                 ELSE null
               END) AS bim2,
           AVG(CASE n.notabimestre
                 WHEN 3 THEN n.nota
                 ELSE null
               END) AS bim3,
           AVG(CASE n.notabimestre
                 WHEN 4 THEN n.nota
                 ELSE null
               END) AS bim4
      FROM notas 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

he returns me:

inserir a descrição da imagem aqui

How do I concatenate the id of the note getting like this:

ALUNO   |DISCIPLINA     |id1|bim1|id2|bim2|id3|bim3|id4|bim4
GUSTAVO |BANCO DE DADOS |1  |7   |2  |7   |3  |7   |4  |8

Note id and in front note do this for the 4 bimonthes. Thanks in advance!

php mysql sql database

  • You see, the two-month note field can be composed of more than one record. You would like to show all?

  • wanted to show everyone

2 answers

1


Use the function GROUP_CONCAT with a CASE:

SELECT a.alunonome AS aluno,
       m.materianome AS disciplina,
       GROUP_CONCAT(CASE n.notabimestre
                      WHEN 1 THEN notaid
                    END) AS notaidbim1,
       AVG(CASE n.notabimestre
             WHEN 1 THEN n.nota
             ELSE null
           END) AS bim1,
       GROUP_CONCAT(CASE n.notabimestre
                      WHEN 2 THEN notaid
                    END) AS notaidbim2,
       AVG(CASE n.notabimestre
             WHEN 2 THEN n.nota
             ELSE null
           END) AS bim2,
       GROUP_CONCAT(CASE n.notabimestre
                      WHEN 3 THEN notaid
                    END) AS notaidbim3,
       AVG(CASE n.notabimestre
             WHEN 3 THEN n.nota
             ELSE null
           END) AS bim3,
       GROUP_CONCAT(CASE n.notabimestre
                      WHEN 4 THEN notaid
                    END) AS notaidbim4,
       AVG(CASE n.notabimestre
             WHEN 4 THEN n.nota
             ELSE null
           END) AS bim4
  FROM notas 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

GROUP_CONCAT

This Function Returns a string result with the Concatenated non-NULL values from a group. It Returns NULL if there are no non-NULL values.

In free translation:

This function returns the string resulting from concatenating non-null values into a grouping. It will return NULL if no non-null values exist.

  • 1

    Raccoon Sorack you are ninja. That’s just what I needed. I never heard of GROUP_CONCAT was really worth the help!

0

SELECT a.alunonome AS aluno, 
       m.materianome AS disciplina,
       **campoID-DaNota as id1,**
       AVG(CASE n.notabimestre
             WHEN 1 THEN n.nota
             ELSE null
           END) AS bim1,

field D-Danota2 as id2, AVG(CASE n.notabimestre WHEN 2 THEN n.note ELSE null END) AS 2, CampoID-Danota3 as id3, AVG(CASE n.notabimestre WHEN 3 THEN n.note ELSE null END) AS 3, campoID-Danota4 as id4, AVG(CASE n.notabimestre WHEN 4 THEN n.note ELSE null END) AS 4 FROM notes n INNER JOIN materials m ON m.materiaid = n.materiaid INNER JOIN students a ON a.alunoid = n.alunoid GROUP BY n.materiaid, n.alunoid, a.alunonome, m.materianome

Browser other questions tagged php mysql sql database

You are not signed in. Login or sign up in order to post.