I have a table called Grades in my database for recording pupil grades as points for every year and term they attend. These points are converted to GCSE results using a lookup table. Although I can return the Points using a pivot table, I'm not sure how to return a table subsituting the points for GCSE grades.
My Grades table looks like this:
PupilID, GradeSubject, YearAndTerm, Grade 10001, English, Y7T1, 81 10001, English, Y7T2, 85 10001, English, Y7T3, 92
My GradesToPoints lookup table looks like this:
PointGrade, GCSEGrade 80, E- 81, E 82, E+ 83, D- 84, D
I have the following SQL statement which returns a table with the columns I need but with points, I want each column (Y7T1, Y7T2, etc) to return the GCSE grade for that point by using the lookup table GradesToPoints.
SELECT * FROM ( SELECT PupilID, GradeSubject, YearAndTerm, Grade FROM Grades ) AS DT PIVOT(SUM(Grade) FOR YearAndTerm IN ([KeyStage2],[Y7T1],[Y7T2],[Y7T3], [Y8T1],[Y8T2],[Y8T3],[Y9T1],[Y9T2],[Y9T3],[Y10T1],[Y10T2],[Y10T3], [Y11T1],[Y11T2],[Y11T3],[Y12T1],[Y12T2],[Y12T3],[Y13T1],[Y13T2], [Y13T3],[Y14T1],[Y14T2],[Y14T3])) AS PVT
Thanks in advance for any help you can provide with this