http://stackoverflow.com/questions/9068600/sql-server-pivot-mulitple-tables

0

Please login or register to vote for this query.

(click on this box to dismiss)

Lifehacks

Q&A for people looking to bypass life's everyday problems with simple tricks

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

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results