SQL> with t as( 2 select 1001 id,'张三' name,'语文' course,69 grade from dual union all 3 select 1001,'张三','数学',99 from dual union all 4 select 1001,'张三','物理',85 from dual union all 5 select 1001,'张三','生物',98 from dual union all 6 select 1001,'张三','化学',100 from dual union all 7 select 1002,'李华','数学',65 from dual union all 8 select 1002,'李华','物理',85 from dual union all 9 select 1002,'李华','生物',70 from dual union all 10 select 1002,'李华','化学',85 from dual) 11 select id 12 ,name 13 ,nvl(max(decode(course,'语文',grade)),0) "语文" 14 ,nvl(max(decode(course,'数学',grade)),0) "数学" 15 ,nvl(max(decode(course,'英语',grade)),0) "英语" 16 ,nvl(max(decode(course,'物理',grade)),0) "物理" 17 ,nvl(max(decode(course,'生物',grade)),0) "生物" 18 ,nvl(max(decode(course,'化学',grade)),0) "化学" 19 from t 20 group by id,name 21 / ID NAME 语文 数学 英语 物理 生物 化学 ---------- ---- ---------- ---------- ---------- ---------- ---------- ---------- 1001 张三 69 99 0 85 98 100 1002 李华 0 65 0 85 70 85
http://topic.csdn.net/u/20110525/22/a4804099-3258-43d0-a46a-b476f2c04142.html
SQL> with t as(
2 select 1001 id,'张三' name,'语文' course,69 grade from dual union all
3 select 1001,'张三','数学',99 from dual union all
4 select 1001,'张三','物理',85 from dual union all
5 select 1001,'张三','生物',98 from dual union all
6 select 1001,'张三','化学',100 from dual union all
7 select 1002,'李华','数学',65 from dual union all
8 select 1002,'李华','物理',85 from dual union all
9 select 1002,'李华','生物',70 from dual union all
10 select 1002,'李华','化学',85 from dual)
11 select id
12 ,name
13 ,nvl(max(decode(course,'语文',grade)),0) "语文"
14 ,nvl(max(decode(course,'数学',grade)),0) "数学"
15 ,nvl(max(decode(course,'英语',grade)),0) "英语"
16 ,nvl(max(decode(course,'物理',grade)),0) "物理"
17 ,nvl(max(decode(course,'生物',grade)),0) "生物"
18 ,nvl(max(decode(course,'化学',grade)),0) "化学"
19 from t
20 group by id,name
21 /
ID NAME 语文 数学 英语 物理 生物 化学
---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
1001 张三 69 99 0 85 98 100
1002 李华 0 65 0 85 70 85