A SELECT using another table's rows to create and name columns – Select every student in "Blue" class with a separate column for each subject taken

I’m struggling to know where to start on this one. I want to write a SELECT query which will return all the students in a particular class and the subjects they each take. But the subjects are stored in rows of another table and I need to somehow make them become a new column for every subject.

I have four tables:

Class Groups:

Students:

Classes:

Class_attendees

I want to write a SELECT query that shows me the timetable for Blue Class that would look like this.

Since no-one in Blue Class is taking Spanish I don’t need a column for it.

>Solution :

I want to write a SELECT query which will return all the students in a particular class and the subjects they each take. But the subjects are stored in rows of another table and I need to somehow make them become a new column for every subject

I want to write a SELECT query that shows me the timetable for Blue Class…

The first part is a straightforward JOIN – but the second part ("…make them become a new column" and "shows me the timetable for") is not possible in SQL nor is it supported by the relational-model that SQL is based on, because the "timetable" view you describe is not an example of relational-data _{(btw, the term "relational" does not refer to "relationships" or foreign-keys; please see Codd’s paper I linked to).}

One of the fundamental rules here (paraphrased) is that columns are static (i.e. your SQL query defines the columns directly: e.g. a, b, c in SELECT a, b c FROM d), so when dealing with data that may-or-may-not-exist (or instead, not-only exists, but has a very high cardinality) then that’s all in the rows/tuples.

So your internal data representation of these undefined columns based on your not-yet-known-in-advance query results cannot have these dynamic columns, but that’s okay because the job of rendering a table/data-grid with dynamic columns is best done by your presentation layer (presumably PHP to generate a HTML <table> or some Report/templating engine you’re using?) – but not using only SQL.

(Yes, you can hack it to work by using Dynamic SQL but let’s just not go there. There is also PIVOT/UNPIVOT, but that still requires you to know what the columns are in-advance when you write the query).

Anyway, the first part is easily done:

SELECT
    s.LastName,
    s.FirstName,
    c.ClassName
FROM
    Class_attendees AS ca
    INNER JOIN Students AS s ON ca.Student_ID = s.ID
    INNER JOIN Classes  AS c ON ca.Class_Id   = c.ID
WHERE
    s.ClassID = 1
ORDER BY
    s.LastName,
    s.FirstName,
    s.ID /* Using this as a tie-breaker if two students have the same name */,
    c.ClassName

…which will give you this:

…and as said, the task of flattening this into that "timetable" view will be done by your presentation-layer and cannot be done using SQL.

…but if you don’t care about de jure columns, you can still flatten the results using GROUP_CONCAT:

WITH studentClasses AS (

    SELECT
        s.ID AS StudentId,
        s.LastName,
        s.FirstName,
        c.ClassName
    FROM
        Class_attendees AS ca
        INNER JOIN Students AS s ON ca.Student_ID = s.ID
        INNER JOIN Classes  AS c ON ca.Class_Id   = c.ID
    WHERE
        s.ClassID = 1
)
SELECT
    g.LastName,
    g.FirstName,
    GROUP_CONCAT( g.ClassName ORDER BY g.ClassName SEPARATOR ', ' ) AS Classes
FROM
    studentClasses AS g
GROUP BY
    g.StudentId /* One should always prefer to GROUP BY actual keys rather than _plain ol' data_ columns; in this case, it means we avoid the pitfull of grouping distinct students that have the same name. */
    /* I assume you're using a modern version of MySQL that detects functional-dependencies in GROUP BY clauses (see https://dev.mysql.com/doc/refman/8.4/en/group-by-functional-dependence.html ) */
ORDER BY
    g.LastName,
    g.FirstName,
    g.StudentId

Gives you…