Dynamic SELECT with fields from second table

Question

I have THREE tables:

• Table_1 with student names (fields = StudentID, FirstName, LastName)

• Table_2 with teacher names (fields = TeacherID, FirstName, LastName, Initials*) (*The 'Initials' field holds each teachers initials: 'JD' for 'John Doe', 'FB' for 'Fred Bloggs', etc)

• Table_3 with the grades that each teacher gave each student (fields = StudentID, TeacherID, Grade)

I would like to generate a query that lists all the students, along with their grades; something like:

FirstName | LastName | JD | FB
==============================
Paul      | Smith    |  7 |  8
Nancy     | Brown    |  5 |  4
Chris     | Nobody   |  6 |  9

The problem is that the columns after FirstName and LastName should be generated "dynamically" from the contents of Table_2 (the teachers giving out the grades). In other words, if a new teacher is added to that table (say, Zack Zanny), then (after he has graded all the students) the same query should yield something like:

FirstName | LastName | JD | FB | ZZ
===================================
Paul      | Smith    |  7 |  8 |  3
Nancy     | Brown    |  5 |  4 |  1
Chris     | Nobody   |  6 |  9 |  2

Did I make this clear?

I've searched this forum and others and I've tried reading up on mysql pivot tables but I must be doing something wrong... Any pointers would be much appreciated. Thanks in advance.

Answer 1

You can't write an SQL query that dynamically expands its columns based on the data values it finds. The columns in the select-list must be fixed at the time you prepare the query.

So you need to know all the teacher id's before you write the query. One way to do this is to query them:

SELECT TeacherID FROM Table_2;

Then based on the result of that query, your application code needs to build the pivot SQL query with as many columns as you need. In other words, use a loop, appending one column to the select-list for each TeacherID returned by the previous query.

SELECT st.FirstName, st.LastName,
  MAX(CASE TeacherId WHEN 'JD' THEN Grade END) AS `JD`,
  MAX(CASE TeacherId WHEN 'FB' THEN Grade END) AS `FB`,
  MAX(CASE TeacherId WHEN 'ZZ' THEN Grade END) AS `ZZ`
FROM Table_1 AS st
LEFT OUTER JOIN Table_3 AS gr ON st.StudentId = gr.StudentId
GROUP BY st.FirstName, st.LastName;

The MAX() functions are just to satisfy the rule of aggregate queries: every column in the select-list must either by named in the GROUP BY clause, or else be within an aggregation function like MAX(). I assume there will be one grade per teacher per student, so I could just as well have used MIN() or even AVG().

To do a pivot query, you must write the columns explicitly in your query, but you can automate that process a bit by first discovering which teacher ID's exist.

The alternative is to write a query that joins students to grades, which means your app will fetch multiple rows per student — one row per teacher.

SELECT st.FirstName, st.LastName, TeacherId, Grade
FROM Table_1 AS st
LEFT OUTER JOIN Table_3 AS gr ON st.StudentId = gr.StudentId;

Then you loop over the result in your app, and present it in a tabular way however you want.

There's no way around it, to do a pivot, you either write some app code that builds the query before you prepare it, or else you use a fixed query but you write some app code to tabulate results after you fetch the raw data.

Either way, the code isn't rocket science.

Answer 2

Dynamic pivoting is not natively supported in MySQL, you have to use dynamic sql :

• query the teachers table to list all ids and initials
• use the results to build a SQL query
• run the query

This SO post gives a working example, that I adapted for your use case as follows :

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(g.TeacherID = ''',
      TeacherID,
      ''', g.grade, NULL)) AS ',
      Initials
    )
  ) INTO @sql
FROM table_2;

SET @sql = CONCAT('SELECT 
        s.firstname,
        s.lastname,
        ', @sql, ' 
    FROM
        table_3 g
        INNER JOIN table_1 s ON s.studentid = g.studentid
    GROUP BY 
        s.studentid,
        s.firstname,
        s.lastname';

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;