Normalize data from an SQL Table

Question

I have student assessment data in a table which looks like

Year   Semester   Studentid   AssessmentCode    Hdg1    Hdg2    Result
2015      2          122         10ENG          Ass1    Raw       80
2015      2          122         10ENG          Ass1    Grade     B
2015      2          122         10ENG          Ass1    Mpd       85
2015      2          122         10ENG          Ass1    Rank      25
2015      2          122         10ENG          Ass2    Raw       78
2015      2          122         10ENG          Ass2    Grade     B
2015      2          122         10ENG          Ass2    Mpd       83
2015      2          122         10ENG          Ass2    Rank      28
2015      2          287         10ENG          Ass1    Raw       70
2015      2          287         10ENG          Ass1    Grade     C
2015      2          287         10ENG          Ass1    Mpd       78
2015      2          287         10ENG          Ass1    Rank      43
2015      2          287         10ENG          Ass2    Raw       82
2015      2          287         10ENG          Ass2    Grade     B
2015      2          287         10ENG          Ass2    Mpd       88
2015      2          287         10ENG          Ass2    Rank      10

and so on...

This is really difficult to work with and use analytical software to look for trends, performance changes etc. I really need to normalize the data to look something like

Year   Semester   Studentid   AssessmentCode    Hdg1   Raw  Grade  Mpd  Rank
2015      2          122         10ENG          Ass1   80     B     85   25
2015      2          122         10ENG          Ass2   78     B     83   28
2015      2          287         10ENG          Ass1   70     C     78   43
2015      2          287         10ENG          Ass2   82     B     88   10

I have created a query using a pivot expression which looks like this...

 SELECT Year,
        Semester,
        StudentID, 
        AssessmentCode,
        Hdg1,   
        [Raw],
        [Grade], 
        [Mpd], 
        [Rank]
FROM (Select vStudentReportsSemesterResults.Year,
            vStudentReportsSemesterResults.Semester, 
            vStudentReportsSemesterResults.studentID, 
            vStudentReportsSemesterResults.AssessmentCode, 
            vStudentReportsSemesterResults.Hdg1,    
            vStudentReportsSemesterResults.Hdg2,
            vStudentReportsSemesterResults.Result
            from vStudentReportsSemesterResults ) sq
             PIVOT (Max (sq.Result) FOR sq.Hdg2 IN ([Raw], [Grade], [Mpd], [Rank])) AS pt
where AssessAreaHdgAbbrev1 like 'Ass%'
order by AssessmentCode, Hdg1, studentid

Now this works, buts takes quite a while to return results (approx 1 minute for 300,000 rows) I am getting time out problems when I try to run it on occasions.

Is there a better way of setting this up which is more efficient and quicker to run? I am thinking inner joins but I am not sure of the syntax required and how I can set this up as I am thinking you will need 4 joins to get the 4 columns of 'Raw', 'Grade', 'Mpd' and 'Rank'. Can someone help with the syntax and structure of such a query.

Is there a better way than inner joins? If there is a more efficient method of normalizing this table could you please let me know?

Self join! Ideally you should have stored columns Raw, Grade, Mpd and Rank from the start. — jarlh, Jul 06 '15 at 09:10
Can you post an explain? I'm curious if it runs the subselect on every row you do. — Tschallacka, Jul 06 '15 at 09:12
@jarhl - Yes I understand that but the table is not mine. It is generated by the commercial software we use to keep students results plus all other details about students. — David Phillips, Jul 06 '15 at 09:12
@Michael Dibbets - sorry Michael not sure what you mean 'Can you post an explain'??? — David Phillips, Jul 06 '15 at 09:15
http://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan — Tschallacka, Jul 06 '15 at 09:18
@Michael Dibbets - how would you like me to show you the Execution Plan - I don't have enough points yet to send it as an image - sorry for the hassle. — David Phillips, Jul 06 '15 at 09:27

score 0 · Answer 1 · answered Jul 06 '15 at 10:49

I think indexes would help this query. Try creating an index on vStudentReportsSemesterResults(AssessAreaHdgAbbrev1, year, semester, studentId, AssessmentCode, Hdg1).

If the "v" at the beginning of the table name really means "view", then your performance issue is probably with the view and not with the pivot. In that case, dump the view into a temporary table and run the pivot on that table. Alternatively, an indexed/materialized view might help.

Normalize data from an SQL Table

1 Answers1