0

I am working on a "builder" where we can add elements to build a quiz.

An element represents a block (a text, a question, a video etc) in the quiz. Each element can be linked to multiple exams with a position in this exam. When creating an element, in my form I can select multiple exams (from the exam table) and enter, for each exam a position (as a number).

I would like to be able to list in a table (in the front-end) all the elements with a column name for each exam name, and below each exam name, the position I entered in the form.

I have actually written this query :

SELECT 
e.id AS element_id, 
e.name AS element_name, 
ee.position, 
se.title AS exam_title
FROM element e
LEFT JOIN element_exams ee
ON e.id = ee.element_id
LEFT JOIN subject_exam se ON se.id = ee.exam_id
GROUP BY e.id

Which gives this result :

enter image description here

However, instead of "exam_title" as a column name, I would like to actually have "AQA Only" and below the position (1.12). And this, for each exam I have in my "exam" table. So for example, if I have 3 exams named (AQA Only, Exam 2, Exam 3), I would like to have a table result with :

element_id | element_name | AQA Only | Exam 2 | Exam 3

And below each exam name, the position I entered in the form.

Is it possible? Basically, I want to have a select query with dynamic column name based on the values of another table.

Thanks!

fraxool
  • 3,199
  • 4
  • 31
  • 57
  • 1
    This is a Pivot table problem. General advice is to consider handling data display related requirements in your application code (eg: PHP, Java etc). However, the duplicate marked question have variety of answers; and you can notice that they are complex as well. – Madhur Bhaiya Sep 27 '19 at 08:17
  • 1
    Topicstarter the general advice from @MadhurBhaiya to handle this in the application is indeed true but it also depends on the resultset size offcource as you don't want to stream hunderds/thousands of megabytes/gigbytes off data between MySQL and the application.. Then indexing / MAX() GROUP BY pivot method/approach which are explained in the duplication link would be most likely more effective – Raymond Nijland Sep 27 '19 at 08:29
  • ... Also to bad MySQL does not support [materialized view](https://en.wikipedia.org/wiki/Materialized_view) with table binding which would allow a (disk stored) view automatically to be updated when table data changes as that would be a great approach for this. well you can [emulate](http://www.coding-dude.com/wp/databases/creating-mysql-materialized-views/) it more or less.. – Raymond Nijland Sep 27 '19 at 08:36

0 Answers0