Postgres - SQL query to extract a cross-tab like functionality from many-to-many mapping

Question

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.

Edouard · Accepted Answer · 2022-03-16 21:30:01Z

1

Static solution based on a fix list of subjects :

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

Dynamic solution when the list of subjects may evolve :

see How to dynamically add columns in PostgresSQL select?

answered Mar 16, 2022 at 21:30

Edouard

7,1291 gold badge11 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres - SQL query to extract a cross-tab like functionality from many-to-many mapping

1 Answer 1

Static solution based on a fix list of subjects :

Dynamic solution when the list of subjects may evolve :

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Static solution based on a fix list of subjects :

Dynamic solution when the list of subjects may evolve :

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related