mysql transform row to column with condition

Question

is it possible to convert mysql rows to columns in this manner?

suppose i have a table like this

+-----+--------------+---------+--------------+-------+
| id  | std_no       | exam_id | subject_code | score |
+-----+--------------+---------+--------------+-------+
|   1 | 1000         |       1 |          101 |    70 |
|   2 | 1000         |       1 |          102 |    75 |
|   3 | 1000         |       1 |          121 |    75 |
|   4 | 1000         |       1 |          231 |    69 |
|   7 | 1001         |       1 |          101 |    80 |
|   8 | 1001         |       1 |          102 |    70 |
|   9 | 1001         |       1 |          121 |    90 |
|  10 | 1001         |       1 |          231 |    80 |
| 127 | 1000         |       2 |          101 |    61 |
| 128 | 1000         |       2 |          102 |    85 |
| 129 | 1000         |       2 |          121 |    50 |
| 130 | 1000         |       2 |          231 |    54 |
| 133 | 1001         |       2 |          101 |    63 |
| 134 | 1001         |       2 |          102 |    14 |
| 135 | 1001         |       2 |          121 |    90 |
| 136 | 1001         |       2 |          231 |    25 |
+-----+--------------+---------+--------------+-------+

and i need to create a new table based on the above like this:

+-----+----------------+-------------+-------+-------+
| id  | std_no         |subject_code | exam1 | exam2 |         
+-----+----------------+-------------+-------+-------+
| 1   | 1000           | 101         | 70    | 61    |
| 2   | 1000           | 102         | 75    | 85    |
| 3   | 1000           | 121         | 75    | 50    |
| 4   | 1000           | 231         | 69    | 54    |
| 5   | 1001           | 101         | 80    | 63    |
| 6   | 1001           | 102         | 70    | 14    |
| 7   | 1001           | 121         | 90    | 90    |
| 8   | 1001           | 231         | 80    | 25    |
+-----+----------------+-------------+-------+-------+

as in go through the first table,get the std_no,subject_code,and score.however i need to put the score inside table2 under exam1 if table1.exam_id=1 and in table2 under exam2 if table1.exam_id=2

is this possible with mysql ?

Do you actually want to create a new table with those columns, or rather just produce results that are laid out with the exams listed as columns? Are the number exams fixed (are there only 2 exams) or is it dynamic? — Martin, Jun 17 '15 at 10:54
@Martin Parkin. there will be more exam columns so it is dynamic — GOA, Jun 17 '15 at 11:05
@Abhik Chakraborty they are four exam_id. i just put two to reduce on typing — GOA, Jun 17 '15 at 11:06
@GOA You will need to use a pivot table to achieve this. Check out this article http://stackoverflow.com/questions/7674786/mysql-pivot-table for some help. — Martin, Jun 17 '15 at 11:12
@MartinParkin .i will produce a new table and thanks for the pivot table pointer. — GOA, Jun 17 '15 at 11:34

score 2 · Accepted Answer · answered Jun 17 '15 at 11:22

For dynamic exam_id you need to create a dynamic query for generating the pivot table something as

set @sql = null;
select
  group_concat(distinct
    concat(
      'max(case when exam_id = ''',
      exam_id,
      ''' then score end) AS ',
      concat('exam',exam_id)
    )
  ) into @sql
from exam;

set @sql = concat('select std_no,subject_code, ', @sql, ' from exam 
                  group by std_no,subject_code
                  order by std_no,subject_code
');

prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;

In the expected result set you also have some id incremental order and to get that you can alter the above query to use user defined variable for the same as

set @sql = null;
select
  group_concat(distinct
    concat(
      'max(case when exam_id = ''',
      exam_id,
      ''' then score end) AS ',
      concat('exam',exam_id)
    )
  ) into @sql
from exam;

set @sql = concat('select @rn:=@rn+1 as id,std_no,subject_code, ', @sql, ' from exam,(select @rn:=0)x
                  group by std_no,subject_code
                  order by std_no,subject_code
');

prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;

DEMO

tested your code in fiddle and it seems to work like i need.i just first have to wrap my head around the new concepts of sql variables and prepared statements then try it on the live database — GOA, Jun 17 '15 at 11:38

Vishal JAIN · Answer 2 · 2015-06-17T10:59:24.177

0

These query gives you records somewhat similar to your output with null values

SELECT id,std_no,subject_code,
  (CASE exam_id WHEN 1 THEN score ELSE NULL END) as exam1,
  (CASE exam_id WHEN 2 THEN score ELSE NULL END) as exam2
FROM table;

edited Jun 17 '15 at 10:59

answered Jun 17 '15 at 10:57

Vishal JAIN

1,940
1
11
14

1

I think the OP is not looking for `some what` result. – Abhik Chakraborty Jun 17 '15 at 10:59
yes sure but it gives Null values thatswhy i am just used this word here – Vishal JAIN Jun 17 '15 at 11:01
Your result does not cater for anything other than exams 1 or 2. The op wishes to dynamically display the exam results in a pivot table. – Martin Jun 17 '15 at 11:11

score 0 · Answer 3 · answered Jun 17 '15 at 11:09

0

You can do this with a self join

SELECT a.id,a.std_no,a.subject_code,
  a.score as exam1,b.score as exam2
FROM table a join table b
  on a.std_no=b.std_no and 
  a.subject_code=b.subject_code 
  WHERE a.exam_id=1 and b.exam_id=2;

answered Jun 17 '15 at 11:09

Joswin K J

690
1
7
16

Your result only shows for exams 1 and 2 - this would not allow for the display of exams 3, 4, etc. dynamically as requested. – Martin Jun 17 '15 at 11:11
@MartinParkin yeah. Noticed the comments just now – Joswin K J Jun 17 '15 at 11:24

score 0 · Answer 4 · answered Jun 17 '15 at 11:15

0

Just use conditional aggregation:

SELECT MIN(id) as id, std_no, subject_code,
       MAX(CASE WHEN exam_id = 1 THEN score END) as exam1,
       MAX(CASE WHEN exam_id = 2 THEN score END) as exam2
FROM table
GROUP BY std_no, subject_code;

answered Jun 17 '15 at 11:15

Gordon Linoff

1,242,037
58
646
786

mysql transform row to column with condition

4 Answers4