SQL Server Pivot Multiple Tables

Question

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?

Any reason Pete Vo is not included in the output? Why is English not included? — Cade Roux, Jan 30 '12 at 18:16
I didn't include Pete Vo because I just wanted to give a general idea of what I wanted to achieve. I've at least over 200 students which are taking at least eight courses per period. — Frida, Jan 30 '12 at 19:05

score 0 · Answer 1 · answered Jan 30 '12 at 18:19

Query out the numbers and courses with a join so you end up with

StudentID CourseDec Grade
1         Math      20
1         Woodwork  82

Pivot that you end up with

StudentID Math WoodWork
1         20   82

Then join Back to student to get First Name Alst Name etc.

score 0 · Accepted Answer · answered Jan 30 '12 at 18:30

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;

wow, each student is taking the same 13 courses the first period, but the last two periods they will be taking different courses ... thanks for your reply. :) — Frida, Jan 30 '12 at 19:07

score 0 · Answer 3 · edited Jan 18 '21 at 12:32

https://data.stackexchange.com/stackoverflow/query/60493/http-stackoverflow-com-questions-9068600-sql-server-pivot-mulitple-tables

DECLARE @Student_Records AS TABLE (
  studentid INT,
  courseid  INT,
  period    VARCHAR(2),
  grade     INT);

INSERT INTO @Student_Records
VALUES      (12,
             6010,
             'P1',
             90),
            (23,
             6020,
             'P1',
             80),
            (12,
             6030,
             'P2',
             NULL),
            (15,
             6010,
             'P1',
             70),
            (12,
             6020,
             'P1',
             80),
            (15,
             6020,
             'P1',
             90);

DECLARE @Course_Records AS TABLE (
  courseid  INT,
  coursedec VARCHAR(50),
  credits   INT);

INSERT INTO @Course_Records
VALUES      ( 6010,
              'Math',
              3),
            ( 6020,
              'Biology',
              3),
            ( 6030,
              'English',
              3);

DECLARE @Student_Info AS TABLE (
  studentid INT,
  firstname VARCHAR(50),
  lastname  VARCHAR(50),
  classyear INT);

INSERT INTO @Student_Info
VALUES      (12,
             'Joe',
             'Smith',
             2013),
            (15,
             'Chak',
             'Li',
             2013),
            (23,
             'Pete',
             'Vo',
             2013);

SELECT DISTINCT coursedec
FROM   @Course_Records AS cr
       INNER JOIN @Student_Records sr
         ON sr.courseid = cr.courseid
WHERE  sr.grade IS NOT NULL;

SELECT classyear,
       lastname,
       firstname,
       summary.studentid,
       summary.math,
       summary.biology
FROM   (SELECT *
        FROM   (SELECT si.studentid,
                       coursedec,
                       grade
                FROM   @Course_Records AS cr
                       INNER JOIN @Student_Records sr
                         ON sr.courseid = cr.courseid
                       INNER JOIN @Student_Info si
                         ON si.studentid = sr.studentid
                WHERE  sr.grade IS NOT NULL) AS results PIVOT (AVG(grade) FOR
               coursedec
               IN (
               [Math], [Biology])) AS pvt) AS summary
       INNER JOIN @Student_Info si
         ON summary.studentid = si.studentid

Note that you can use dynamic HTML to make the query adjust as more courses are added:

Pivot Table and Concatenate Columns

nice Data.SE query but you might want to remove the first result set since you need to choose text-only-results to see more than one result set [when a data.se query has multple result sets](http://meta.stackexchange.com/questions/74289/add-support-for-multiple-results-sets-in-data-explorer) — Conrad Frix, Jan 30 '12 at 19:13

SQL Server Pivot Multiple Tables

3 Answers3