/*********橫向查詢oracle和sql通用**********/
drop table students;
drop table score;
 
create table students
( stu_code char(3) constraint pk_students primary key,
 stu_name varchar(20),
 stu_sex char(1)
);
 
create table score
( stu_code char(3) constraint fk_score_students references students(stu_code),
 sub_name varchar(10),
 stu_score numeric(4,1),
 constraint pk_score primary key (stu_code,sub_name)
);
 
insert into students values('001','張三',0);
insert into students values('002','李四',1);
insert into students values('003','王五',0);
insert into students values('004','張可阿',1);
insert into students values('005','王三分',1);
insert into students values('006','李渴望',1);
 
insert into score values('001','語文',85);
insert into score values('002','語文',76);
insert into score values('003','語文',32);
insert into score values('004','語文',78);
insert into score values('005','語文',45);
insert into score values('006','語文',34);
insert into score values('001','數學',55);
insert into score values('002','數學',66);
insert into score values('003','數學',79);
insert into score values('004','數學',45);
insert into score values('005','數學',41);
insert into score values('006','數學',29);
insert into score values('001','英語',56);
insert into score values('002','英語',54);
insert into score values('003','英語',78);
insert into score values('004','英語',95);
insert into score values('005','英語',25);
insert into score values('006','英語',34);
 
select Stu_Code,sum(Chinese) 語文,sum(Math) 數學,sum(English) 英語
from
(Select stu_code,stu_score Chinese,0 Math,0 English From score Where sub_name='語文'
union
Select stu_code,0 Chinese,stu_score Math,0 English From score Where sub_name='數學'
union
Select stu_code,0 Chinese,0 Math,stu_score English From score Where sub_name='英語') A
Group By Stu_Code
/*********效果顯示***************/
STU       語文       數學       英語
--- ---------- ---------- ----------
001         85         55         56
002         76         66         54
003         32         79         78
004         78         45         95
005         45         41         25
006         34         29         34