By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE mdl_user (
id INT,
idnumber VARCHAR(50),
firstname VARCHAR(50),
lastname VARCHAR(50)
);
INSERT INTO mdl_user
VALUES
(11, '4432433', 'Johan', 'Smith');
CREATE TABLE mdl_grade_items (
id INT,
idnumber VARCHAR(50),
grademax INT
);
INSERT INTO mdl_grade_items
VALUES
(21, '148', 25),
(22, '414', 25);
CREATE TABLE mdl_grade_grades (
userid INT,
itemid INT,
finalgrade INT
);
INSERT INTO mdl_grade_grades
VALUES
(11, 21, 25),
(11, 21, 5),
(11, 22, 24);
6 rows affected
SELECT *,
CAST(gg.finalgrade AS FLOAT) / gi.grademax AS fractional_grade
FROM mdl_grade_grades AS gg
LEFT JOIN mdl_grade_items gi ON gg.itemid = gi.id
LEFT JOIN mdl_user u ON gg.userid = u.id
| userid | itemid | finalgrade | id | idnumber | grademax | id | idnumber | firstname | lastname | fractional_grade |
|---|---|---|---|---|---|---|---|---|---|---|
| 11 | 21 | 25 | 21 | 148 | 25 | 11 | 4432433 | Johan | Smith | 1 |
| 11 | 21 | 5 | 21 | 148 | 25 | 11 | 4432433 | Johan | Smith | 0.2 |
| 11 | 22 | 24 | 22 | 414 | 25 | 11 | 4432433 | Johan | Smith | 0.96 |
SELECT
PIVT.[first name], PIVT.surname, PIVT.[id number],
-- Only now, scale and formatting as percent.
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
| first name | surname | id number | 148 | 414 |
|---|---|---|---|---|
| Johan | Smith | 4432433 | 100% | 96% |