I am using the SQL query below to retrieve some quiz data from a Moodle database.
My test data in the database is:
| first name | surname | id number | grade_code | result |
|---|---|---|---|---|
| Johan | Smith | 4432433 | 148 | 100% |
| Johan | Smith | 4432433 | 148 | 20% |
| Johan | Smith | 4432433 | 414 | 96% |
As you can see, there are TWO rows containing a result for grade_code 148 (100% and 20%) and ONE row containing a result for grade_code 414 (96%).
The MS SQL Server SQL query that gives me the above test data is:
SELECT
u.firstname AS 'first name',
u.lastname AS 'surname',
u.idnumber AS 'id number',
gi.idnumber AS 'grade_code',
CASE gg.finalgrade
WHEN NULL THEN 'No result'
ELSE CAST(CAST(ROUND(((gg.finalgrade/gi.grademax)*100),0) AS INTEGER) AS NVARCHAR(10)) + '%'
END AS 'result'
FROM mdl_grade_grades AS gg
INNER JOIN mdl_grade_items gi ON gg.itemid = gi.id
INNER JOIN mdl_user u ON gg.userid = u.id
WHERE gi.idnumber IN (
'148','414'
)
AND u.idnumber = '4432433'
When I pivot the above SQL, like this:
SELECT * FROM (
SELECT
u.firstname AS 'first name',
u.lastname AS 'surname',
u.idnumber AS 'id number',
gi.idnumber AS 'grade_code',
CASE gg.finalgrade
WHEN NULL THEN 'No result'
ELSE CAST(CAST(ROUND(((gg.finalgrade/gi.grademax)*100),0) AS INTEGER) AS NVARCHAR(10)) + '%'
END AS 'result'
FROM mdl_grade_grades AS gg
INNER JOIN mdl_grade_items gi ON gg.itemid = gi.id
INNER JOIN mdl_user u ON gg.userid = u.id
WHERE gi.idnumber IN (
'148','414'
)
AND u.idnumber = '4432433'
) SOURCE
PIVOT (
MAX(grade)
FOR grade_code IN (
[148],[414]
)
) PIVT
the output result I get is:
| first name | surname | id number | 148 | 414 |
|---|---|---|---|---|
| Johan | Smith | 4432433 | 20% | 96% |
The problem is that it is returning the row with 20% for grade_code 148. I want it to return the HIGHEST grade_code (100%), for each grade_code like this:
| first name | surname | id number | 148 | 414 |
|---|---|---|---|---|
| Johan | Smith | 4432433 | 100% | 96% |
So, when there are multiple rows for each grade_code the query must return the one row with the highest value for that grade_code.
Is the fix for this just to find a way to ORDER BY the grade_code column?
My first guess was to add a
ORDER BY '148','414'
to the end of the pivot query, but that is not valid.
Nor is this valid:
ORDER BY [148],[414]
Or this:
ORDER BY grade_code
What do I do to fix this? How to I always return the one highest result for each grade_code?
1
You should perform your grade calculations and pivot operations using a numeric type (not text), leaving the calculated score as calculated (finalgrade/grademax) in the range 0..1. The percentage scaling (x 100) and formatting is best deferred to the presentation layer. or in this case, the final select list since there is no presentation layer.
The null case can be handled using the ISNULL() function.
SELECT
PIVT.[first name], PIVT.surname, PIVT.[id number],
ISNULL(CAST(CAST(ROUND(PIVT.[148] * 100, 0) AS INTEGER) AS NVARCHAR(10)) + '%',
'No result') AS [148],
ISNULL(CAST(CAST(ROUND(PIVT.[414] * 100, 0) AS INTEGER) AS NVARCHAR(10)) + '%',
'No result') AS [414]
FROM (
SELECT
u.firstname AS [first name],
u.lastname AS surname,
u.idnumber AS [id number],
gi.idnumber AS [grade_code],
CAST(gg.finalgrade AS FLOAT) / gi.grademax AS fractional_grade
FROM mdl_grade_grades AS gg
INNER JOIN mdl_grade_items gi ON gg.itemid = gi.id
INNER JOIN mdl_user u ON gg.userid = u.id
WHERE gi.idnumber IN (
'148','414'
)
AND u.idnumber = '4432433'
) SOURCE
PIVOT (
MAX(fractional_grade)
FOR grade_code IN (
[148],[414]
)
) PIVT
Because I was unsure about your source data types, I added a cast to float before the division in the above calculation.
Results (using some reverse engineered test data):
| first name | surname | id number | 148 | 414 |
|---|---|---|---|---|
| Johan | Smith | 4432433 | 100% | 96% |
See this db<>fiddle for a demo.
4