Converting rows to columns in mysql

Question

I am struggling to dynamically convert the row <-> column in mysql. I have looked a few questions in SO and the nearest I could find is this: MySQL pivot row into dynamic number of columns

This is the original table:

+-----------+------------+----------+
|  name     |  type      |   marks  |
+-----------+------------+----------+
|  thaqif   |  Quiz 1    |   5.oo   |
+-----------+------------+----------+
|  ajis     |  Quiz 1    |   5.00   |
+-----------+------------+----------+
|  thaqif   |  Test      |   25.00  |
+-----------+------------+----------+
|  ajis     |  test      |   25.00  |
+-----------+------------+----------+

My desired output would be like this:

+-----------+------------+----------+
|  name     |  Quiz 1    |   Test   |
+-----------+------------+----------+
|  thaqif   |  5.00      |   25.oo  |
+-----------+------------+----------+
|  ajis     |  5.00      |   25.00  |
+-----------+------------+----------+

This is the code that I have modified:

SET @SQL = NULL;

SELECT
    GROUP_CONCAT(
        CONCAT('count(case when assessment_type = ''',assessment_type,''' then 1 end) AS ',replace(assessment_type, '', '')
        )
    ) INTO @sql
    from studentmarks;

SET @sql = CONCAT('SELECT student_name,', @sql, ' from studentmarks');

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

However, the output just displayed one row and the marks was not even correct. I believed there would be something wrong in the count() function but then, removing the function will show the failed to call getClauses() function.

UPDATED: The reason why I am looking for dynamic sql is that, the number of assessment type is unknown. Maybe, there would be a presentation or even an assignment inserted by the user. Therefore, the table should be able to display those type of assessment and its marks too

You don't need dynamic sql for the sample you have given but you do need a group by. — P.Salmon, Mar 12 '18 at 07:21

score 0 · Answer 1 · edited Mar 12 '18 at 05:48

0

i am giving you simple demonstration you should try like this

select s1.name, s1.marks as 'Quiz 1',s2.marks as 'Test' from studentmarks as s1 join studentmarks as s2 on s1.name = s2.name and s2.type = 'test' where s1.type = 'Quiz 1'

edited Mar 12 '18 at 05:48

Rahul

1,617
1
9
18

answered Mar 12 '18 at 05:01

Purushottam zende

552
1
6
20

score 0 · Answer 2 · answered Mar 12 '18 at 08:38

There are a number of things wrong with your statement build 1) count is not appropriate use max 2) of marks 3) you are not catering for the situation where a student does not have an assessment type - the case should have an else 0 4) the replace does not remove spaces in the assessment type 5) the group_concat should include a distinct clause to avoid duplicates 6) there is no group by in the final select

Converting rows to columns in mysql

2 Answers2