SQL Rollup - I think

Question

I have a table of student results in a MS SQL database. Each student result is made up of a level and grade. I have attached a screen shot. The intReportID field is crucial to tying them together. The key is TblReportsStoreGradesID

In my output I want the level and grade to appear in the same row. Can someone give me a pointer please because I am lost.

Thanks

Answer 1

Your ROLLUP intuition isn't right, but is heading is the right direction.

You want to collapse multiple rows to single rows; that's aggregation, so GROUP BY. Then you need some logic to determine which row's value goes to which column in the output, you haven't explained that, so I'll just use MIN() and MAX()

SELECT
  g.intReportID,
  MIN(g.txtGrade)   txtGrade_min,
  MAX(g.txtGrade)   txtGrade_max
FROM
  TblReportsStore        AS s
INNER JOIN
  TblReportsStoreGrades  AS g
    ON g.intReportID = s.TblReportsStoreID
WHERE
  s.intReportCycle = 68
GROUP BY
  g.intReportID

If you have other useful columns, you might be able to use something like this instead (to pick which rows go in to which columns)...

MAX(CASE WHEN g.value_type = 'level' THEN g.txtGrade END)  AS level,
MAX(CASE WHEN g.value_type = 'grade' THEN g.txtGrade END)  AS grade

Answer 2

SELECT MAX(CASE WHEN g.intGradeID = 32 THEN g.txtGrade END) AS level, MAX(CASE WHEN g.intGradeID = 33 THEN g.txtGrade END) AS grade, TblPupilManagementPupils.txtForename, TblPupilManagementPupils.txtSurname, TblTeachingManagerSubjects.txtSubjectName FROM TblReportsStore AS s INNER JOIN TblReportsStoreGrades AS g ON g.intReportID = s.TblReportsStoreID INNER JOIN TblPupilManagementPupils ON s.txtSchoolID = TblPupilManagementPupils.txtSchoolID INNER JOIN TblTeachingManagerSets ON s.txtSubID = TblTeachingManagerSets.txtSetCode INNER JOIN TblTeachingManagerSubjects ON TblTeachingManagerSets.intSubject = TblTeachingManagerSubjects.TblTeachingManagerSubjectsID WHERE (s.intReportCycle = 68) GROUP BY g.intReportID, TblPupilManagementPupils.txtForename, TblPupilManagementPupils.txtSurname, TblTeachingManagerSubjects.txtSubjectName