0

Let's say that I have this one table which contain the student marks for each assessment (eg: quiz,test and etc).

This is the original table:

studentmarks

I wanted to convert the assessment type to be the column. This is the output that I want:

+---------------+------------+----------+
|  student_name |  Quiz 1    |   QUIZ 2 |
+---------------+------------+----------+
|  thaqif       |  4.00      |   5.oo   |
+---------------+------------+----------+
|  ajis         |  4.00      |   5.00   |
+---------------+------------+----------+

I am able to produce this with this algorithm:

SELECT  student_name,
        MAX(IF((`assessment_type` = 'QUIZ' AND `assessmet_no` = '1'), assessment_marks, NULL)) 'QUIZ 1',
        MAX(IF((`assessment_type` = 'QUIZ' AND `assessmet_no` = '2'), assessment_marks, NULL)) 'QUIZ 2'            
FROM    studentmarks
GROUP   BY student_name

However, in the real situation, the number of assessment type is unknown. The algorithm above is only useful if we have the fixed number of assessment_type.

In order to handle that, I have found an algorithm which I believed can solve the problem:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(
    CONCAT(
      'MAX(IF(`assessment_type` = ', `assessment_type`, ',assessment_marks, NULL)) AS ', `assessment_type`)
  ) INTO @sql
FROM studentmarks;

SET @sql = CONCAT('SELECT  student_name, ', @sql, ' 
                  FROM    studentmarks
                  GROUP   BY student_name');

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

Unfortunately, this algorithm called this error:

#1054 - Unknown column 'QUIZ' in 'field list'

I have no idea on what's wrong with the algorithm. Fyi, I have gone through a few similar questions on SO but none of it seems to be working with my problem. These are some of the questions or information that I referred to:

  1. MySQL pivot row into dynamic number of columns

  2. Mysql query to dynamically convert rows to columns on the basis of two columns

  3. Mysql query to dynamically convert rows to columns

  4. http://buysql.com/mysql/14-how-to-automate-pivot-tables.html

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Mohammad Thaqif Taher
  • 45
  • 2
  • 9

1 Answers1

1

This is only slightly more complicated than a regular pivot because you need a condition on two columns:

select student_name,
       max(case when assessment_type = 'QUIZ' and assesment_no = 1 then marks end) as quiz1,
       max(case when assessment_type = 'QUIZ' and assesment_no = 2 then marks end) as quiz2
from student_marks
group by student_name;

You can expand this into dynamic SQL as:

SET @condition = 'MAX(CASE WHEN assessment_type = ''@at'' AND assement_no = @an THEN assessment_marks END) as @at@an';

SELECT @conditions := GROUP_CONCAT(REPLACE(REPLACE(@condition, '@at', assessment_type), '@an', assessment_no))
FROM studentmarks;

SET @sql = CONCAT('
SELECT student_name, ', @conditions, ' 
FROM studentmarks
GROUP BY student_name');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786