1. 行转列

建表语句

CREATE table student(
id int(8) primary key auto_increment,
name varchar(50) default null comment '姓名',
subject varchar(20)  default null comment '科目',
score double default 0 comment '分数'
);

测试数据

INSERT into student (name,subject,score) values ('张三','语文',99);
INSERT into student (name,subject,score) values ('张三','数学',100);
INSERT into student (name,subject,score) values ('张三','英语',87);
INSERT into student (name,subject,score) values ('李四','语文',89);
INSERT into student (name,subject,score) values ('李四','数学',97);
INSERT into student (name,subject,score) values ('李四','英语',88);

原始数据查询

SELECT * from student ;

行转列SQL语句：

SELECT name , MAX(CASE subject when '语文' then score else 0 end) as '语文', MAX(CASE subject when '数学' then score else 0 end) as '数学', MAX(CASE subject when '英语' then score else 0 end) as '英语' FROM student group by name;

 2. 列转行

建表语句

CREATE table student2(
id int(8) primary key auto_increment,
name varchar(50) default null comment '姓名',
subject1 varchar(20)  default null comment '语文',
subject2 varchar(20)  default null comment '英语',
subject3 varchar(20)  default null comment '英语'
);

测试数据

insert into student2 (name,subject1,subject2,subject3) values('张三','99','100','87');
insert into student2 (name,subject1,subject2,subject3) values('李四','89','97','88');

原始数据查询

SELECT * from student2 ;

 列转行SQL语句：

两种方式：

SELECT name,'语文' as subject , subject1 as score from student2
UNION 
SELECT name,'数学' as subject , subject2 as score from student2
UNION 
SELECT name,'英语' as subject , subject3 as score from student2 
order by name ;

或

SELECT 
    name ,
    '语文' as subject,
    MAX(subject1) as score
from
    student2
group by
    name
UNION 
SELECT 
    name ,
    '数学' as subject,
    MAX(subject2) as score
from
    student2
group by
    name
UNION 
SELECT 
    name ,
    '英语' as subject,
    MAX(subject3) as score
from
    student2
group by
    name
ORDER BY
    name ;