----創建測試表

create table student_score(

name varchar2(20),

subject varchar2(20),

score number(4,1)

);

-----插入測試數據

insert into student_score (name,subject,score)values('張三','語文',78);

insert into student_score (name,subject,score)values('張三','數學',88);

insert into student_score (name,subject,score)values('張三','英語',98);

insert into student_score (name,subject,score)values('李四','語文',89);

insert into student_score (name,subject,score)values('李四','數學',76);

insert into student_score (name,subject,score)values('李四','英語',90);

insert into student_score (name,subject,score)values('王五','語文',99);

insert into student_score (name,subject,score)values('王五','數學',66);

insert into student_score (name,subject,score)values('王五','英語',91);

-----decode行轉列

select name "姓名",

sum(decode(subject, '語文', nvl(score, 0), 0)) "語文",

sum(decode(subject, '數學', nvl(score, 0), 0)) "數學",

sum(decode(subject, '英語', nvl(score, 0), 0)) "英語"

from student_score

group by name;

------ case when 行轉列

select name "姓名",

sum(case when subject='語文'

then nvl(score,0)

else 0

end) "語文",

sum(case when subject='數學'

then nvl(score,0)

else 0

end) "數學",

sum(case when subject='英語'

then nvl(score,0)

else 0

end) "英語"

from student_score

group by name;

總結

以上是生活随笔為你收集整理的行转列 oracle nvl,oracle 行转列 decode的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。