I have a data-set consisting of Student and Subject in a PostgreSQL Database. The relations look something like:
Student:
id,
name,
...
Student ID | Name | ...
1 | Ramesh | ...
2 | Suresh | ...
Subject:
id,
name,
...
Subject ID | Name | ...
1 | Maths | ...
2 | Science | ...
Student_Subject:
id,
student_id,
subject_id,
...
The third table, as one could imagine, it's a form of representing many-to-many mapping. Suppose a student has selected 3 subjects, he will have 3 mappings against his ID in the student_subject table.
There is no restriction on the number of subjects that a student may select it could be anything between 0 and 10.
I need to create a single SQL query, that will fetch records in this format:
Student ID | Student Name | Maths | Science | ... | History 1 | Ramesh | Y | N | ... | Y 2 | Suresh | N | Y | ... | Y
The Subject names could be hardcoded as column aliases that's fine.
Can someone pls suggest how this can be achieved?
I tried using the case when technique as:
select
stud.name,
(case when sub.name = 'Maths' then 'Y' else 'N' end) "Maths",
(case when sub.name = 'Science' then 'Y' else 'N' end) "Science",
...
from student stud
inner join student_subject s_s on s_s.student_id = stud.id
inner join subject sub on sub.id = s_s.student_id
;
But this way I'm not getting one row per student. If the student has selected 3 subjects, I'm getting 3 different rows with one Y value against each of the subjects on each row.
You have to group the resulting rows by student so that to get one row per student, and for each group, use the bool_or() aggregate function which returns True when at least on row in the group returns True :
select
stud.name,
bool_or(case when sub.name = 'Maths' then True else False end) "Maths",
bool_or(case when sub.name = 'Science' then True else False end) "Science",
...
from student stud
inner join student_subject s_s on s_s.student_id = stud.id
inner join subject sub on sub.id = s_s.student_id
group by stud.name