oracle unpivot 索引_oracle 11g 行列转换之unpivot、pivot
一、?? ?列?? ?轉?? ?行
create table STU_ROW2COL
(
id????? VARCHAR2(10),
intname VARCHAR2(10),
subject VARCHAR2(20),
grade?? NUMBER
)
insert into stu_row2col (ID, INTNAME, SUBJECT, GRADE) values ('1', 'ZORRO', '語文', 70);
insert into stu_row2col (ID, INTNAME, SUBJECT, GRADE) values ('2', 'ZORRO', '數學', 80);
insert into stu_row2col (ID, INTNAME, SUBJECT, GRADE) values ('3', 'ZORRO', '英語', 75);
insert into stu_row2col (ID, INTNAME, SUBJECT, GRADE) values ('4', 'SEKER', '語文', 65);
insert into stu_row2col (ID, INTNAME, SUBJECT, GRADE) values ('5', 'SEKER', '數學', 75);
insert into stu_row2col (ID, INTNAME, SUBJECT, GRADE) values ('6', 'SEKER', '英語', 60);
insert into stu_row2col (ID, INTNAME, SUBJECT, GRADE) values ('7', 'BLUES', '語文', 60);
insert into stu_row2col (ID, INTNAME, SUBJECT, GRADE) values ('8', 'BLUES', '數學', 90);
insert into stu_row2col (ID, INTNAME, SUBJECT, GRADE) values ('9', 'PG', '數學', 80);
insert into stu_row2col (ID, INTNAME, SUBJECT, GRADE) values ('10', 'PG', '英語', 90);
SQL>? select t.* from stu_row2col t ;
ID???????? INTNAME??? SUBJECT?????????????????? GRADE
---------- ---------- -------------------- ----------
11???????? ZORRO????? 語文???????????????????????? 77
1????????? ZORRO????? 語文???????????????????????? 70
2????????? ZORRO????? 數學???????????????????????? 80
3????????? ZORRO????? 英語???????????????????????? 75
4????????? SEKER????? 語文???????????????????????? 65
5????????? SEKER????? 數學???????????????????????? 75
6????????? SEKER????? 英語???????????????????????? 60
7????????? BLUES????? 語文???????????????????????? 60
8????????? BLUES????? 數學???????????????????????? 90
9????????? PG???????? 數學???????????????????????? 80
10???????? PG???????? 英語???????????????????????? 90
想要實現行專列， 就是輸出結果是這樣的：
SQL>? select * from ( select t.intname,t.subject,t.grade from stu_row2col t)
pivot(sum(grade) for subject in ('語文' 語文,'數學' 數學,'英語' 英語))? ;
INTNAME??????????? 語文???????? 數學???????? 英語
---------- ---------- ---------- ----------
SEKER????????????? 65???????? 75???????? 60
BLUES????????????? 60???????? 90
PG??????????????????????????? 80???????? 90
ZORRO???????????? 147???????? 80???????? 75
二、?? ?行?? ?轉?? ?列
創建一個視圖：
create or replace view stu_col2row as
select "INTNAME","語文","數學","英語" from ( select t.intname,t.subject,t.grade from stu_row2col t) pivot(sum(grade) for subject in ('語文' 語文,'數學' 數學,'英語' 英語));
SQL>? select INTNAME 姓名,km 科目, fs 分數 from stu_col2row unpivot(fs for km in(語文,數學,英語));
姓名?????? 科目???????? 分數
---------- ---- ----------
SEKER????? 語文???????? 65
SEKER????? 數學???????? 75
SEKER????? 英語???????? 60
BLUES????? 語文???????? 60
BLUES????? 數學???????? 90
PG???????? 數學???????? 80
PG???????? 英語???????? 90
ZORRO????? 語文???????? 70
ZORRO????? 數學???????? 80
ZORRO????? 英語???????? 75
總結
以上是生活随笔為你收集整理的oracle unpivot 索引_oracle 11g 行列转换之unpivot、pivot的全部內容，希望文章能夠幫你解決所遇到的問題。
