Setting up an SQL for School Report?

Question

I am trying to mount an SQL to display the results of a School Report Card. In the table of notes I have the data as follows.

Table Notes

MATERIA   |  NOTA  |  PERIODO
PORTUGUES |   5.0  |    1Bim
PORTUGUES |   10.0 |    2Bim
PORTUGUES |   8.0  |    3Bim
PORTUGUES |   9.0  |    4Bim

To display this data I want it to look like this

MATERIA   |   1Bim    |   2Bim   |   3Bim   |    4Bim
PORTUGUES |    5.0    |   10.0   |   8.0    |    9.0

I tried to group everything however, as there are these bimesters not working, and I am not able to think of a way to do this directly in SQL, because I use this SQL as a return in a JSON.

The SQL I’m trying is like this.

SELECT NT_MATERIA, NT_PERIODO, NT_MATRICULA, NT_NOTAFINAL,
MATRICULA.M_CODALUNO, MATRICULA.M_ANOLETIVO, 
ALU_CODIGO, ALU_NOME,
MATERIAS.M_CODIGO, MATERIAS.M_DESCRICAO,
PE_ID, PE_DESCRICAO
FROM NOTAS NOTA
INNER JOIN MATRICULA ON (NOTA.NT_MATRICULA = MATRICULA.M_CODIGO)
INNER JOIN ALUNOS ON (MATRICULA.M_CODALUNO = ALUNOS.ALU_CODIGO)
INNER JOIN MATERIAS ON (NOTA.NT_MATERIA = MATERIAS.M_CODIGO)
INNER JOIN PERIODO ON (NOTA.NT_PERIODO = PERIODO.PE_ID)
WHERE (ALUNOS.ALU_CODIGO = 238) AND (MATRICULA.M_ANOLETIVO = 2015)
GROUP BY NT_MATERIA, NT_PERIODO, NT_MATRICULA, NT_NOTAFINAL, 
MATRICULA.M_CODALUNO, MATRICULA.M_ANOLETIVO, 
ALU_CODIGO, ALU_NOME,
MATERIAS.M_CODIGO, MATERIAS.M_DESCRICAO,
PE_ID, PE_DESCRICAO
ORDER BY MATERIAS.M_DESCRICAO

How can I do that ?

Answer 1

Hello,

follow the example:

declare @tabela table
(
    MATERIA varchar (40),
    NOTA decimal (10,2),
    PERIODO varchar (40)
)

insert into @tabela values 
('PORTUGUES ',   5.0  ,    '1Bim'),
('PORTUGUES ',    10.0 ,   '2Bim'),
('PORTUGUES ',    8.0  ,    '3Bim'),
('PORTUGUES ',    9.0  ,    '4Bim')


select MATERIA, Bim1 as'1 Bim', Bim2 as '2 Bim',  Bim3 as '3 Bim' ,Bim4 as '4 Bim'
from 
(
    select materia ,
        sum(case when PERIODO = '1Bim' then NOTA end) as Bim1,
        sum(case when PERIODO = '2Bim' then NOTA end) as Bim2,
        sum(case when PERIODO = '3Bim' then NOTA end) as Bim3,
        sum(case when PERIODO = '4Bim' then NOTA end) as Bim4
     from @tabela
     group by materia
)D