Solved

Looking to pivot grades table

Posted on 2013-06-24
6
283 Views
Last Modified: 2014-01-13
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
0
Comment
Question by:jswanberg
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
6 Comments
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 250 total points
ID: 39270569
select
  StudentID
--, CourseCode
, CourseName
, max(case when TermCode = 'Q1' then GradeEarned end) as Q1
, max(case when TermCode = 'Q2' then GradeEarned end) as Q2
, max(case when TermCode = 'Q3' then GradeEarned end) as Q3
, max(case when TermCode = 'Q4' then GradeEarned end) as Q4
from YourTable
group by
  StudentID
--, CourseCode
, CourseName

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39270574
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.
0
 
LVL 11

Expert Comment

by:Louis01
ID: 39270891
--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

0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 250 total points
ID: 39271147
Oracle Syntax (11g) for use of the pivot feature, if it is preferred:
SELECT *
FROM   (SELECT STUDENTID,COURSENAME, TERMCODE, GRADEEARNED
        FROM   YOURTABLE)
PIVOT  (MAX(GRADEEARNED) AS GRADE FOR (TERMCODE)  IN ('Q1' AS Q1, 'Q2' AS Q2, 'Q3' AS Q3, 'Q4' AS q4));

Open in new window

0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to recover a database from a user managed backup

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question