SQL行转列

1.行转列

1.经典案例：

新建一个数据表

create table STUDENT_SCORE
(
name VARCHAR2(20),
subject VARCHAR2(20),
score NUMBER(4,1)
)
insert into student_score (NAME, SUBJECT, SCORE) values ('张三', '语文', 78.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('张三', '数学', 88.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('张三', '英语', 98.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('李四', '语文', 89.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('李四', '数学', 76.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('李四', '英语', 90.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('王五', '语文', 99.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('王五', '数学', 66.0);
insert into student_score (NAME, SUBJECT, SCORE) values ('王五', '英语', 91.0);

得到类似下面的结果：
姓名 语文 数学 英语
王五 89 56 89

 

select ts.name,

sum(decode(ts.subject,'语文',ts.score)) "语文",

sum(decode(ts.subject,'数学',ts.score)) "数学",

sum(decode(ts.subject,'英语',ts.score)) "英语"

from test_score ts

group by ts.name