Orcal行转列
例子:
CREATE TABLE StudentScores
(
UserName VARCHAR(20), --学生姓名
Subject VARCHAR(30), --科目
Score FLOAT --成绩
)
insert into STUDENTSCORES (USERNAME, SUBJECT, SCORE)
values (‘张三’, ‘语文’, 80);
insert into STUDENTSCORES (USERNAME, SUBJECT, SCORE)
values (‘张三’, ‘数学’, 90);
insert into STUDENTSCORES (USERNAME, SUBJECT, SCORE)
values (‘张三’, ‘英语’, 70);
insert into STUDENTSCORES (USERNAME, SUBJECT, SCORE)
values (‘李四’, ‘语文’, 80);
insert into STUDENTSCORES (USERNAME, SUBJECT, SCORE)
values (‘李四’, ‘数学’, 92);
insert into STUDENTSCORES (USERNAME, SUBJECT, SCORE)
values (‘李四’, ‘英语’, 76);
方法一:
select t.username,
sum((case trim(t.subject) when ‘数学’ then t.score end)) as 数学,
sum((case trim(t.subject) when ‘语文’ then t.score end)) as 语文,
sum((case trim(t.subject) when ‘英语’ then t.score end)) as 英语
from STUDENTSCORES t
group by t.username;
方法二