row to column conversion in mysql

Question

Below is my table structure and I want to convert it into another format (From row to column type) I have tried very much but I am not able to do so.

StudentID | Mark | Subject
-------------------------
10        |46    |Java
--------------------------
10        |65    |C#
--------------------------
10        |79    |JavaScript
---------------------------
11        |66    |Java
--------------------------
11        |85    |C#
--------------------------
11        |99    |JavaScript
--------------------------

O/P Should be:

StudentID | Java | C# | JavaScript
---------------------------------
10        |  46  | 65 |   79
---------------------------------
11        |  66  | 85 |  99
-------------------------------

"have tried very much but not able to do so" What have you tried? What is your problem in doing so? More details please. — feeela, Jan 14 '13 at 11:33
You should check for pivot table : http://stackoverflow.com/questions/1241178/mysql-rows-to-columns — Panciz, Jan 14 '13 at 11:37

score 5 · Answer 1 · answered Jan 14 '13 at 11:35

This type of data transformation is known as a pivot. MySQL does not have a pivot function, so you will want to transform the data using an aggregate function with a CASE expression.

If you know the values ahead of time to transform, then you can hard-code them similar to this:

select studentid,
  sum(case when subject = 'Java' then mark else 0 end) Java,
  sum(case when subject = 'C#' then mark else 0 end) `C#`,
  sum(case when subject = 'JavaScript' then mark else 0 end) JavaScript
from yourtable
group by studentid

See SQL Fiddle with Demo.

If the values of the subject are unknown or flexible, then you might want to use a prepared statement to generate dynamic sql:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'sum(case when subject = ''',
      subject,
      ''' then mark else 0 end) AS `',
      subject, '`'
    )
  ) INTO @sql
FROM  yourtable;

SET @sql = CONCAT('SELECT studentid, ', @sql, ' 
                  from yourtable
                  group by studentid');

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

See SQL Fiddle with Demo.

The result for both queries is:

| STUDENTID | JAVA | C# | JAVASCRIPT |
--------------------------------------
|        10 |   46 | 65 |         79 |
|        11 |   66 | 85 |         99 |

Thanks for your answer but I am looking for generic one not by harcoding "JAVA"or "C#" code should pick automatically ... — Prabhat Shankar, Jan 14 '13 at 11:37
@PrabhatShankar I was just adding a dynamic version as well. See my edit — Taryn, Jan 14 '13 at 11:39
Thanks it is possible to do by some temporary table without using prepared statement — Prabhat Shankar, Jan 14 '13 at 11:45
The problem is that you have to create the query string dynamically based on the values in the table. Even with a temp table you have to generate the dynamic string to execute — Taryn, Jan 14 '13 at 11:47

row to column conversion in mysql

1 Answers1

Linked