How to exclude duplicate values ​​from the PIVOT operator?

Question

SQL

Hello! there is a table (like an e-journal), I wanted to display data through a cross-request, but the fields are repeated

SELECT  ID_KEY2,[2017-10-27],[2017-10-28]
FROM 
(SELECT ID, ID_KEY2, OCENKA, DAT
FROM Journal2) p
PIVOT
(AVG(OCENKA) FOR DAT IN ( [2017-10-27],[2017-10-28] ) ) AS pvt ORDER BY pvt.ID_KEY2;

ID_KEY2 is the student's identifier
OCENKA - The grade he received
DAT - date format, the number of the grade received
Here I just made a request for myself, and here ID_KEY2 is repeated.
For example, a student with ID_KEY2 = 1 got a grade of 5 on 10/27/2017 and a 4
on 10/28/2017 date 28-10-2017
I can't make it to be in a row once in the corresponding columns
What am I doing wrong?
Thank you all in advance! :)

Answer 1

Well, remove the extra id before doing Pivot
(SELECT ID, ID_KEY2, OCENKA, DAT
FROM Journal2) p - here you have a subquery from which data is taken. Since simply writing DISTINCT will not work, it must be redone so that you have one date for each id.