Frida Frida - 1 month ago 8
SQL Question

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?

Answer

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;