SQL Rollup - I think-CodePudding

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

CodePudding user response：

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

CodePudding user response：

Thank you.

The first row in the pair will be the level and the second the grade. I'm not sure I get the use of min and max.

Kev