Ò»µÀoracle²éѯÎÊÌ⣿ - Oracle / ¸ß¼¶¼¼Êõ

id name kecheng fenshu
1 ÕÅÈý ÓïÎÄ 89
1 ÕÅÈý Êýѧ 90
1 ÕÅÈý Ó¢Óï 99
2 ÍõÎå ÓïÎÄ 80
2 ÍõÎå Êýѧ 99
2 ÍõÎå Ó¢Óï 88

Ôõôת±ä
id name ÓïÎÄ Êýѧ Ó¢Óï
1 ÕÅÈý 89 90 99
2 ÍõÎå 80 99 88


³ýÁË
select distinct t.id,t.name,
(select distinct t1.fenshu from emp3 t1 where t1.id=t.id and t1.kecheng='ÓïÎÄ') ÓïÎÄ,
(select distinct t1.fenshu from emp3 t1 where t1.id=t.id and t1.kecheng='Êýѧ') Êýѧ,
(select distinct t1.fenshu from emp3 t1 where t1.id=t.id and t1.kecheng='Ó¢Óï') Ó¢Óï
 from emp3 t
»¹ÓÐûÓиü¼òµ¥µÄ£¿£¿

---

select id,name,
max(decode(kecheng ,'ÓïÎÄ',fenshu)) ÓïÎÄ,
max(decode(kecheng ,'Êýѧ',fenshu)) Êýѧ,
max(decode(kecheng ,'Ó¢Óï',fenshu)) Ó¢Óï
from emp3
grop by id,name;

---

ÒýÓÃ

id name kecheng fenshu
1 ÕÅÈý ÓïÎÄ 89
1 ÕÅÈý Êýѧ 90
1 ÕÅÈý Ó¢Óï 99
2 ÍõÎå ÓïÎÄ 80
2 ÍõÎå Êýѧ 99
2 ÍõÎå Ó¢Óï 88

Ôõôת±ä
id name ÓïÎÄ Êýѧ Ó¢Óï
1 ÕÅÈý 89 90 99
2 ÍõÎå 80 99 88


³ýÁË
select distinct t.id,t.name,
(select di¡­¡­

SQL code:

--case,decode º¯Êý
select id,name,max(case when kecheng='ÓïÎÄ' then fenshu end) ÓïÎÄ,
max(case when kecheng='Êýѧ' then fenshu end) Êýѧ,
max(case w