MySQL行列转换

先创建表

CREATE TABLE `grade` (
  `name` text,
  `course` text,
  `score` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入数据

INSERT INTO `grade` (`name`,`course`,`score`) VALUES('张三','语文',76);
INSERT INTO `grade` (`name`,`course`,`score`) VALUES('李四','语文',76);
INSERT INTO `grade` (`name`,`course`,`score`) VALUES('李四','数学',90);
INSERT INTO `grade` (`name`,`course`,`score`) VALUES('张三','数学',75);

列转行

select name,
	max(case when course = "数学" then score else 0 end) "数学",
    max(case when course = "语文" then score else 0 end) "语文" 
from grade group by `name`;

创建另一张表及插入数据

/*建表*/
create table test.p_grade(
	`name` varchar(255),
    `math` int,
    `chinese` int
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*将列转行的结果作为新的结果插入表中*/
insert into test.p_grade (select name,
	max(case when course = "数学" then score else 0 end) "数学",
    max(case when course = "语文" then score else 0 end) "语文" 
from test.grade group by `name`);

行转列

/* database名字为test */
select `name`, '语文' `subject`, chinese from test.p_grade 
union all 
select `name`, '数学' `subject`, math from test.p_grade;