How to join the two tables in this case?

Question

I have two tables a "students" with the enrollment and enrollment and another "faz_teste" with the grades of the students in certain tests. I used the pivot command in the table "faz_teste" to transform the name of the proofs into column and put the notes into rows, 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 registration and grades of the students in each test, as shown in the image:

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

Answer 1

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

see the table link on a.idaluno = ft.idaluno