name subject result
Lily Java 74
Lily Hibernate 83
LiLy Oracle 93
Lucy Java 74
Lucy Hibernate 84
Lucy Oracle 94
fullname Java Hibernate Oracle
LiLy 74 83 93
Lucy 74 84 94
select name as 'fullname',
max(case subject when 'Java' then result else 0 end) as Java,
max(case subject when 'Hibernate' then result else 0 end) as Hibernate,
max(case subject when 'Oracle' then result else 0 end) as Oracle
from table
group by name
实例2:
表数据
id name subject score1
22 张三 语文 81
23 张三 数学 75
24 李四 语文 76
25 李四 数学 90
26 王五 语文 81
27 王五 数学 100
28 王五 英语 90
查询
select
sc.name as 姓名,
max(case sc.subject when '语文' then sc.score1 else 0 end) as 语文,
max(case sc.subject when '数学' then sc.score1 else 0 end) as 数学,
max(case sc.subject when '英语' then sc.score1 else 0 end) as 英语,
sum(nvl(sc.score1,0)) as sumScore,
round(avg(nvl(sc.score1,0)),2) as avgScore
from score sc
group by sc.name;
结果
姓名 语文 数学 英语 SUMSCORE AVGSCORE
张三8175015678
王五811009027190.33
李四7690016683