Pivot table in MySQL - convert to pivot table with values in varchar format

Question

i would like convert data from Table1 as you can see on first picture on data in Pivot_table. Is possible to do that in MySQL? Because the values of pivot table(A, B, C, D) are in varchar data format and i cannot use any aggregation function of MySQL like SUM or others.

Table1:
PK        Name     Subject     Grade
-------------------------------------
1         Bob       Math        A
2         Bob       History     B
3         Bob       Language    C
4         Bob       Biology     D
5         Sue       History     C
6         Sue       Math        A
7         Sue       Music       A
8         Sue       Geography   C


Pivot_table:
Subject     Bob     Sue
-------------------------
Math        A        A
History     B        C
Language    C 
Biology     D
Music                A
Geography            C

Thanks for your help

Is the Pivot_table static? Or you create it on fly? – StanislavL Sep 05 '13 at 06:50 — StanislavL, Sep 05 '13 at 06:50

peterm · Accepted Answer · 2013-09-05T07:10:03.943

A static query (as far as only Bob and Sue are concerned) might look like this

SELECT subject, 
       MAX(CASE WHEN name = 'Bob' THEN grade END) `Bob`,
       MAX(CASE WHEN name = 'Sue' THEN grade END) `Sue` 
  FROM table1 
 GROUP BY subject

Now to be able to account for other names use dynamic SQL like this

SET @sql = NULL;

SELECT GROUP_CONCAT(DISTINCT
         CONCAT('MAX(CASE WHEN name = ''', name,
                ''' THEN grade END) `', name, '`'))
  INTO @sql
  FROM table1;

SET @sql = CONCAT('SELECT subject, ', @sql, ' 
                     FROM table1 
                    GROUP BY subject');

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

Output:

|   SUBJECT |    BOB |    SUE |
|-----------|--------|--------|
|   Biology |      D | (null) |
| Geography | (null) |      C |
|   History |      B |      C |
|  Language |      C | (null) |
|      Math |      A |      A |
|     Music | (null) |      A |

Here is SQLFiddle demo

You can wrap it into a stored procedure to simplify things on the calling end

DELIMITER $$
CREATE PROCEDURE sp_grade_report()
BEGIN
  SET @sql = NULL;

  SELECT GROUP_CONCAT(DISTINCT
           CONCAT('MAX(CASE WHEN name = ''', name,
                  ''' THEN grade END) `', name, '`'))
    INTO @sql
    FROM table1;

  SET @sql = CONCAT('SELECT subject, ', @sql, ' 
                       FROM table1 
                      GROUP BY subject');

  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

Sample usage:

CALL sp_grade_report();

Here is SQLFiddle demo

score 0 · Answer 2 · answered Sep 05 '13 at 06:56

Write a stored procedure for the convertion.

SELECT DISTINCT name FROM table1; to get list of columns for the Pivot_table. Create the table with appropriate number of columns + 1 for Subject.
Insert all the subjects in the pivot table to get 1 row for each subject. INSERT ... from SELECT DISTINCT subject FROM table1.
Create cursor to retrieve all the records from the table1. Go through all the rows of the cursor and for each row call UPDATE Pivot_table SET [COLUMN_NAME for studetn value]=[grade value] WHERE subject=[subject name value];

Pivot table in MySQL - convert to pivot table with values in varchar format

2 Answers2

Linked