Orcal行转列

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;

方法二： select t.username, sum(decode(t.subject, ‘数学’, t.score)) 数学, sum(decode(t.subject, ‘语文’, t.score)) 语文, sum(decode(t.subject, ‘英语’, t.score)) 英语 from STUDENTSCORES t group by t.username;

注：数据不能存在空格。