Oracle中的行列转换

• 首先建一个表

create table chengji
(
id NUMBER,
name VARCHAR2(20),
course VARCHAR2(20),
score NUMBER
); 

insert into chengji (id, name, course, score)
values (1, '张三', '语文', 67);
insert into chengji (id, name, course, score)
values (1, '张三', '数学', 76);
insert into chengji (id, name, course, score)
values (1, '张三', '英语', 43);
insert into chengji (id, name, course, score)
values (1, '张三', '历史', 56);
insert into chengji (id, name, course, score)
values (1, '张三', '化学', 11);
insert into chengji (id, name, course, score)
values (2, '李四', '语文', 54);
insert into chengji (id, name, course, score)
values (2, '李四', '数学', 81);
insert into chengji (id, name, course, score)
values (2, '李四', '英语', 64);
insert into chengji (id, name, course, score)
values (2, '李四', '历史', 93);
insert into chengji (id, name, course, score)
values (2, '李四', '化学', 27);
insert into chengji (id, name, course, score)
values (3, '王五', '语文', 24);
insert into chengji (id, name, course, score)
values (3, '王五', '数学', 25);
insert into chengji (id, name, course, score)
values (3, '王五', '英语', 58);
insert into chengji (id, name, course, score)
values (3, '王五', '历史', 45);
insert into chengji (id, name, course, score)
values (3, '王五', '化学', 21); 
insert into chengji (id, name, course, score)
values (4, 'Jack', '语文', 86);
insert into chengji (id, name, course, score)
values (4, 'Jack', '数学', 90);
insert into chengji (id, name, course, score)
values (4, 'Jack', '英语', 93);
insert into chengji (id, name, course, score)
values (4, 'Jack', '历史', 77);
insert into chengji (id, name, course, score)
values (4, 'Jack', '化学', 33); 
insert into chengji (id, name, course, score)
values (5, 'Helen', '语文', 89);
insert into chengji (id, name, course, score)
values (5, 'Helen', '数学', 97);
insert into chengji (id, name, course, score)
values (5, 'Helen', '英语', 95);
insert into chengji (id, name, course, score)
values (5, 'Helen', '历史', 73);
insert into chengji (id, name, course, score)
values (5, 'Helen', '化学', 29); 
commit;

看一下表结果

1.dedode函数

decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值)
这个是decode的表达式，具体的含义解释为：

IF 条件=值1 THEN
　　　　RETURN(翻译值1)
ELSIF 条件=值2 THEN
　　　　RETURN(翻译值2)
　　　　......
ELSIF 条件=值n THEN
　　　　RETURN(翻译值n)
ELSE
　　　　RETURN(缺省值)
END IF

SELECT id,name,
sum(decode(course,'语文',score,0)) 语文,
sum(decode(course,'数学',score,0)) 数学,
sum(decode(course,'英语',score,0)) 英语,
sum(decode(course,'历史',score,0)) 历史,
sum(decode(course,'化学',score,0)) 化学,
sum(score) 总成绩
from chengji
GROUP BY id,name
ORDER BY id;

2.case when

case when end编写和维护较麻烦，但是适合的场景较多。

SELECT id,name,
max(case when course='语文' then score else 0 end) 语文,
max(case when course='数学' then score else 0 end) 数学,
max(case when course='英语' then score else 0 end) 英语,
max(case when course='化学' then score else 0 end) 化学,
max(case when course='历史' then score else 0 end) 历史,
sum(score) 总成绩
from chengji
GROUP BY id,name
ORDER BY id;

3.pivot

SELECT * FROM chengji
pivot(max(score) for course in( --course 即要转成列的字段
'语文' as 语文,                 --max(score) 此处必须为聚合函数
'数学' as 数学,                 --in () 对要转成列的每一个值指定一个列名
'英语' as 英语,
'化学' as 化学,
'历史' as 历史
))
WHERE 1=1      --这里可以写查询条件，没有可以直接不要where
ORDER BY id;