SQL Server Pivot Mulitple Tables
I will really appreciate if someone can guide me in the right direction.
We have the following tables:
Table 1: Student_Records
StudentID | CourseID | Period | Grade
12 6010 P1 90
23 6020 P1 80
12 6030 P2 ' ' Blank, there's no grade
15 6010 P1 70
12 6020 P1 80
15 6020 P1 90
Table 2: Course_Records
CourseID CourseDec Credits
6010 Math 3
6020 Biology 3
6030 English 3
Table 3: Student_Info
StudentID FirstName LastName ClassYear
12 Joe Smith 2013
15 Chak Li 2013
23 Pete Vo 2013
Result Desire:
ClassYear LastName FirstName StudentId Math Biology
2013 Smith Joe 12 90 80
2013 Li Chak 15 70 90
How can I achieve this result using the pivot command?
You can use PIVOT for this but it requires that you know which course descriptions you're interested in.
SELECT p.classyear,
p.lastname,
p.firstname,
p.studentid,
pvt.math,
pvt.biology
FROM (SELECT sr.grade,
si.classyear,
si.studentid,
si.firstname,
silastname
FROM student_info si
INNER JOIN student_records sr
ON si.studentid = sr.studentid
INNER JOIN course_records cr
ON sr.courseid = cr.courseid) p PIVOT ( AVG (grade) FOR
coursedec IN (
[Math], [Biology]) ) AS pvt
ORDER BY pvt.classyear;