Concatenate data from a table

Question

Concatenate data from a table

Navigation

#1 by (1 votes)
#2 by (0 votes)

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

It returns me:

How do I concatenate the id of the note thus:

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

id of the note and in front of the note do this for the 4 bimestres. Thank you!

php mysql

asked by anonymous 19.06.2017 / 23:57

2 answers

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,

fieldID-DaNota2 as id2, AVG (CASE n.notabimestre WHEN 2 THEN n.nota ELSE null END) AS bim2, fieldID-DaNota3 as id3, AVG (CASE n.notabimestre WHEN 3 THEN n.nota ELSE null END) AS bim3, fieldID-DaNota4 as id4, AVG (CASE n.notabimestre WHEN 4 THEN n.nota ELSE null END) AS bim4 FROM notes n INNER JOIN materials m ON m.materiaid = n.materiaid INNER JOIN students at ON a.alunoid = n.alunoid GROUP BY n.materiaid, n.alunoid, a.alunonome, m.materianome

20.06.2017 / 00:05

Switchery with tooltips (bootstrap), how to do? multi upload php

score 1 · Accepted Answer

Use the GROUP_CONCAT function 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 with the concatenated non-NULL values from a group. It returns NULL if there are non-NULL values.

Free translation:

This function returns the string resulting from the concatenation of non-null values in a grouping. It will return NULL if there are no non-null values.