How to join the two tables in this case?

2

I have two tables one "students" with enrollment and enrollment and another "do" with the students' grades in certain tests. I used the pivot command in the table "do_test" to transform the name of the tests into columns and the notes in lines, as shown in the following query:

SELECT  inscricao,[1_BIM],[2_BIM],[3_BIM],[4_BIM]
 FROM (
    SELECT inscricao, idteste, nota_num
    FROM [EasyNovo].[dbo].[faz_teste]
    WHERE idteste='1_BIM' OR idteste='2_BIM' OR idteste='3_BIM' OR idteste='4_BIM'
  )tt

PIVOT (SUM(nota_num) 
FOR idteste IN ([1_BIM],[2_BIM],[3_BIM],[4_BIM]))pvt

In the result of this query I have the inscription and the notes of the students in each test, as the image shows:

But I need to, besides the inscription, the name. How to do in this case?

    
asked by anonymous 19.02.2016 / 19:21

1 answer

0

Try to do.

SELECT  inscricao,[1_BIM],[2_BIM],[3_BIM],[4_BIM], Nome
 FROM (
    SELECT inscricao, idteste, nota_num, a.Nome
    FROM [EasyNovo].[dbo].[faz_teste] ft
    join [EasyNovo].[dbo].[alunos] a
    on a.idaluno = ft.idaluno
    WHERE idteste='1_BIM' OR idteste='2_BIM' OR idteste='3_BIM' OR idteste='4_BIM'
  )tt

PIVOT (SUM(nota_num) 
FOR idteste IN ([1_BIM],[2_BIM],[3_BIM],[4_BIM]))pvt

view the connection of table on a.idaluno = ft.idaluno

    
19.02.2016 / 19:55