Ever wondered how to display the following output via an SQL query?
| Student | Calculus | Simmulation | Programming | Comm Skills |
| Ibrahim Itambo | 67 | 98 | 83 | 59 |
| Abdulswamad | 68 | 94 | 77 | 40 |
| Shamim | 70 | 76 | 56 | 44 |
| Samira | 36 | 98 | 68 | 90 |
There are several ways of doing it but the most common and annoying hack is to use CASE...WHEN and/or aggregate functions with GROUP BY.
It may work but they are usualy overly complex, unreadable and hard to maintain.
A better solution in Postgres is to use crosstab function available via the tablefunc module.
Example code
CREATE TABLE student(
student_id serial primary key,
student_name varchar(50),
remarks test
);
INSERT INTO student(student_name) VALUES('Ibrahim');
INSERT INTO student(student_name) VALUES('Kilui');
INSERT INTO student(student_name) VALUES('Ali Babababa');
INSERT INTO student(student_name) VALUES('Buzi');
CREATE TABLE subject(
subject_id serial primary key,
subject_name varchar(50),
remarks text
);
INSERT INTO subject(subject_name) VALUES('Calculus I');
INSERT INTO subject(subject_name) VALUES('Simulation');
INSERT INTO subject(subject_name) VALUES('Adv Programming');
INSERT INTO subject(subject_name) VALUES('Comm Skills');
CREATE TABLE exam(
exam_id serial primary key,
exam_title varchar(50),
student_id integer references student,
subject_id integer references subject,
exam_mark real default 0 not null,
remarks text
);
//FINALY INSERT RESULTS
//INSERT INTO exam(exam_title, student_id, subject_id, exam_mark) VALUES(......); you know what to do here
SELECT * FROM crosstab(
'select student.student_name::text, subject.subject_name, exam.exam_mark
from exam
inner join student on exam.student_id = student.student_id
inner join subject on exam.subject_id = subject.subject_id
order by 1,2',
'SELECT subject_name FROM subject ORDER BY 1')
AS
CT("Student" text, "Calculus" real, "Simulation" real, "Prog" real, "Comm Skills" real);
Adios
