jswanberg
asked on
Looking to pivot grades table
I have a table that stores student grades:
StudentID
CourseCode
CourseName
TermCode
TeacherName
GradeEarned
A student could be given multiple grades for the same course throughout the year with the TermCode being the key.
I'd like to have a SQL statement that pivots this table and returns the data horizontally like a gradebook view:
StudentID----CourseName--- TeacherNam e----Q1--Q 2--Q3--Q4
123435 AlgebraI Mr. Smith A B+ B A-
123435 History Mr. Jones C C+ B- B
The list of possible TermCodes is known so that doesn't need to be dynamic and I do only need to return the four columns of potential term grades.
js
StudentID
CourseCode
CourseName
TermCode
TeacherName
GradeEarned
A student could be given multiple grades for the same course throughout the year with the TermCode being the key.
I'd like to have a SQL statement that pivots this table and returns the data horizontally like a gradebook view:
StudentID----CourseName---
123435 AlgebraI Mr. Smith A B+ B A-
123435 History Mr. Jones C C+ B- B
The list of possible TermCodes is known so that doesn't need to be dynamic and I do only need to return the four columns of potential term grades.
js
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
--Prepare sample table
declare @theTable table( StudentID int
, CourseName varchar(50)
, TermCode char(2)
, GradeEarned varchar(2));
insert into @theTable (StudentID, CourseName, TermCode, GradeEarned) values (1, 'Course 1', 'Q1', 'A');
insert into @theTable (StudentID, CourseName, TermCode, GradeEarned) values (1, 'Course 2', 'Q1', 'B');
insert into @theTable (StudentID, CourseName, TermCode, GradeEarned) values (1, 'Course 1', 'Q1', 'C');
insert into @theTable (StudentID, CourseName, TermCode, GradeEarned) values (1, 'Course 2', 'Q1', 'D');
insert into @theTable (StudentID, CourseName, TermCode, GradeEarned) values (2, 'Course 1', 'Q1', 'A');
insert into @theTable (StudentID, CourseName, TermCode, GradeEarned) values (2, 'Course 2', 'Q1', 'B');
insert into @theTable (StudentID, CourseName, TermCode, GradeEarned) values (2, 'Course 1', 'Q1', 'C');
insert into @theTable (StudentID, CourseName, TermCode, GradeEarned) values (3, 'Course 2', 'Q1', 'D');
insert into @theTable (StudentID, CourseName, TermCode, GradeEarned) values (5, 'Course 1', 'Q1', 'A');
insert into @theTable (StudentID, CourseName, TermCode, GradeEarned) values (6, 'Course 2', 'Q1', 'B');
insert into @theTable (StudentID, CourseName, TermCode, GradeEarned) values (7, 'Course 1', 'Q1', 'C');
insert into @theTable (StudentID, CourseName, TermCode, GradeEarned) values (8, 'Course 2', 'Q1', 'D');
--Pivot table
select StudentID, CourseName
, [Q1], [Q2], [Q3], [Q4]
from (select * from @theTable) t1
pivot (MAX(GradeEarned) FOR TermCode in ([Q1], [Q2], [Q3], [Q4])) as pt1
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Note, sometimes there is a concern about using MAX(...) to achieve these pivots, but it should not be an issue - provided there is only one grade given per student/course/term
The use of MAX() in such a scenario really just allows for reduction from multiple rows to a single row.