CrossTab query in Postgres

Hello,

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