firstmadcoding firstmadcoding - 2 months ago 9
MySQL Question

Display MySQL records horizontally

Generally we fetch data from MySQL table and display the fetched records in rows one by one. But sometimes we need to display the records horizontally as below.

Name * Student-ID * subject-1 * subject-2 * subject-3 * subject-4 * subject-5 ... So On
John 1 20 50 34 50 45
Mercy 3 40 20 82 23 10
James 8 30 60 40 50 30
Agi 19 50 10 23 45 30


Now am looking for a way which can produce the results as mentioned above. Below are MySQL tables which I would like to come-up with the above desired results.

Table subjects Stores subjects by class as follows


  1. subjectid

  2. subjectname

  3. examinationid



Table examinfo Stores examination information follows


  1. assessment_id

  2. idsubject



Table examinations Stores Student Scores alongside relevant information follows


  1. studentid

  2. fname

  3. lname

  4. id_subject

  5. score


    • examinationid == assessment_id

    • id_subject AND idsubject == subjectid




To Query All Students Of a class who sat for examination with id examinationid (1)

SELECT *
FROM examinations AS exam
INNER JOIN examinfo AS info ON info.idsubject = exam.id_subject
WHERE info.assessment_id = 1


The above query will produce results vertically so How to transform or query the table Horizontally. am missing it because I never use SQL in this way before. Thanks for your hand

Answer

You are looking for MYSQL pivot

STATIC WAY: Use conditional case with group by

SELECT CONCAT(exam.fname," ",exam.lname) AS Name, exam.studentid, 
 SUM(CASE WHEN exam.id_subject = 1 THEN exam.score ELSE "NA" END) AS subject1,
 SUM(CASE WHEN exam.id_subject = 2 THEN exam.score ELSE "NA" END) AS subject2,  
 SUM(CASE WHEN exam.id_subject = 3 THEN exam.score ELSE "NA" END) AS subject3,
 SUM(CASE WHEN exam.id_subject = 4 THEN exam.score ELSE "NA" END) AS subject4,  
 SUM(CASE WHEN exam.id_subject = 5 THEN exam.score ELSE "NA" END) AS subject5
FROM examinations AS exam
INNER JOIN examinfo AS info ON info.idsubject = exam.id_subject
WHERE info.assessment_id = 1
GROUP BY exam.studentid

DYNAMIC WAY: Use GROUP_CONCAT with CONCAT

SET @sql = NULL;
SELECT
    GROUP_CONCAT(DISTINCT 
    CONCAT('SUM(CASE WHEN id_subject= "', 
    id_subject, '" THEN score ELSE "NA" END) AS '
    , 'Subject-', id_subject))
INTO @sql
FROM
  examinations;

SET @sql = CONCAT('SELECT CONCAT(fname," ",lname) AS Name, 
                   studentid, ', @sql, ' 
                  FROM examinations
                  INNER JOIN examinfo AS info ON info.idsubject = id_subject
                  WHERE info.assessment_id = 1
                  GROUP BY studentid');

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