oracle行轉列與列轉行
一、行轉列
在有些應用場景中，如學生成績。學生所有科目的成績都保存到一張表裡面，當我們需要以列表的形式顯示出學生所對應的每一科目的成績的時候，需要使用到行轉列。
示例
1 -- 學生成績表
2 create table grade (
3 id varchar2(64) not null,
4 name varchar2(20) not null,
5 course varchar2(100) not null,
6 score number(3) not null,
7 primary key (id)
8 )
9
10 -- 初始化數據
11 insert into grade values( '100001', '張三', '語文', 75);
12 insert into grade values( '100002', '張三', '數學', 80);
13 insert into grade values( '100003', '張三', '英語', 80);
14
15 insert into grade values( '100004', '李四', '語文', 86);
16 insert into grade values( '100005', '李四', '數學', 66);
17 insert into grade values( '100006', '李四', '英語', 81);
18
19 insert into grade values( '100007', '王五', '語文', 92);
20 insert into grade values( '100008', '王五', '數學', 78);
21 insert into grade values( '100009', '王五', '英語', 82);
22
23 insert into grade values( '100010', '趙六', '語文', 80);
24 insert into grade values( '100011', '趙六', '數學', 90);
25 insert into grade values( '100012', '趙六', '英語', 77);
26
27 insert into grade values( '100013', '李明', '語文', 69);
28 insert into grade values( '100014', '李明', '數學', 75);
29 insert into grade values( '100015', '李明', '英語', 80);
行轉列語句
1 select
2 t.name "姓名",
3 sum(decode(t.course, '語文', t.score , 0)) "語文",
4 sum(decode(t.course, '數學', t.score , 0)) "數學",
5 sum(decode(t.course, '英語', t.score , 0)) "英語"
6 from grade t
7 group by t.name
因為這裡使用到了 group by t.name，所以對於要展示score只能使用聚合函數(sum,max,min)都可以，如果不使用聚合函數就會報錯。
執行結果如下：
二、列轉行
與行轉列相反，有些場景剛好需要這種操作。
示例如下：
1 --列轉行
2 create table sc_grade (
3 id varchar2(64) not null,
4 name varchar2(20) not null,
5 cn_score number(3) not null,
6 math_score number(3) not null,
7 en_score number(3) not null,
8 primary key (id)
9 )
10
11 --初始數據
12 insert into sc_grade values ('10001', '張三', 75, 80, 80);
13 insert into sc_grade values ('10002','王五', 92, 78, 82);
14 insert into sc_grade values ('10003','趙六', 80, 90, 77);
15 insert into sc_grade values ('10004','李四', 86, 66, 81);
16 insert into sc_grade values ('10005','李明', 69, 75, 80);
列傳行語句
1 select name "姓名" , '語文' course, cn_score as score from sc_grade
2 union
3 select name "姓名" , '數學' course, math_score as score from sc_grade
4 union
5 select name "姓名" , '英語' course, en_score as score from sc_grade
6 order by "姓名" , course
結果如下：
也可以使用insert all .. into完成。使用insert all ..。 into 需要新建一個表用來保存轉換後的數據。
表結構如下：
1 -- 結果存放表
2 create table test_sc_grade (
3 name varchar2(20) not null,
4 course varchar2(100) not null,
5 score number(3) not null
6 )
執行語句如下：
1 insert all
2 into test_sc_grade(name,course,score) values(name, '語文', cn_score)
3 into test_sc_grade(name,course,score) values(name, '數學', math_score)
4 into test_sc_grade(name,course,score) values(name, '英語', en_score)
5 select name, cn_score, math_score, en_score from sc_grade;
6 commit;
7 select * from test_sc_grade
結果查詢與上面截圖一致。
