Link to home
Start Free TrialLog in
Avatar of jswanberg
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---TeacherName----Q1--Q2--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
SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
this is a "classic pivot" using case when syntax. As your TermCode's are a known list, and you only require 4 grade columns, this seems completely appropriate.

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.
--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
 

Open in new window

ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial