mysql行列转,mysql行列转换

1.一维转二维

上图为成绩表中数据，现希望将数据转换为下图。

①静态：转化为二维表后的列名及列数是确定不变的，本例中即course只有数学、语文、英语这三门课。

select s_name,

max(if(course="数学",score,0)) as 数学,

max(if(course='语文',score,0)) as 语文,

max(if(course='英语',score,0)) as 英语,

sum(score) as 总分

from grade group by s_name;

②动态：转化为二维表后的列名及列数是可变的，本例中即course的课程数不确定。

set @sql='';

select@sql:=concat(@sql,'max(if(course='',course,'',score,0)) as ',course,',')from (select distinct course from grade) as a;

set@strsql=concat('select s_name,',@sql,'sum(score)as 总分 from grade group by s_name;');

prepare stmt from @strsql;

execute stmt;

deallocate prepare stmt;

2.二维转一维

上图为成绩表2中数据，现希望将数据转为成绩表1的数据。

select name,'数学'as course,数学 as score from grade2

union all

select name,'语文'as course,语文 as score from grade2

union all

select name,'英语'as course,英语 as score from grade2

order by name;