行转列.txt

sql 行转列编程,横向查询oracle和sql通用

/*********横向查询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