一、使用decode函數進行“行轉列”：
首先我們來看看最典型的需求：學生成績單報表制作
【1】表結構定義：
create table SCORE
(
ID NUMBER(2),
STUDENT_NAME VARCHAR2(10),
SUBJECT VARCHAR2(10),
SCORE NUMBER(6,2)
)
【2】測試數據如下：
SQL> select * from score;
ID STUDENT_NAME SUBJECT SCORE
---------- --------------- ---------- ----------
1 paul Chinese 80
1 paul Math 75.05
1 paul English 90
2 bob Chinese 85.05
2 bob English 73.05
2 bob Math 99
已選擇6行。
【3】最終轉換的結果如下：
ID STUDENT_NAME 語法 數學 英語
---------- --------------- ---------- ---------- ----------
1 paul 80 75.05 90
2 bob 85.05 99 73.05
【4】實現方式：
SQL> select id,
2 student_name,
3 sum(decode(subject, 'Chinese', score)) 語法,
4 sum(decode(subject, 'Math', score)) 數學,
5 sum(decode(subject, 'English', score)) 英語
6 from score
7 group by id, student_name;
ID STUDENT_NAME 語法 數學 英語
---------- --------------- ---------- ---------- ----------
1 paul 80 75.05 90
2 bob 85.05 99 73.05
SQL>
這里的原理就是利用decode函數對列subject進行判斷，如果等于'Chinese'，則將對應的score列的值累加。同理可知其它兩個字段。
【5】備注：
這種方式有幾個特點：
①用于判斷的字段其所有可能的值必須是已知的，假如是未知數則這里的SQL將不成立。
②用于轉換的字段通常為數值型的，因為像行列轉換的情況通常都會用到累積求和
③用于group by的字段都是主鍵字段，而非用于判斷的字段
【6】延伸需求：
假設現在我們不是要生成成績單報表了，我們要統計一下每個科目參考的人數有那幾個人，其最終的結果形式如下：
SUBJECT STUDENT
------- ---------
Chinese paul, bob
English paul, bob
Math paul, bob
像這種情況，我們就需要重新定義一下Oracle的'sum'函數了，這里就涉及到另外一個專題(以后會再介紹)
二、使用insert...select進行“列轉行”：
【1】表結構定義：
create table score_2
(
id number(2) primary key,
student_name varchar2(10),
chinese number(6,2),
math number(6,2),
english number(6,2)
)
【2】測試數據如下：
SQL> select * from score_2;
ID STUDENT_NAME CHINESE MATH ENGLISH
---------- --------------- ---------- ---------- ----------
1 paul 70 80 90
2 bob 80 90 100
【3】最終轉換的結果如下：
SQL> select * from score;
ID STUDENT_NAME SUBJECT SCORE
---------- --------------- ---------- ----------
1 paul Chinese 80
1 paul Math 75.05
1 paul English 90
2 bob Chinese 85.05
2 bob English 73.05
2 bob Math 99
已選擇6行。
也就是說和第一種情況剛好是相反的。
【4】實現方式：
SQL> set timing on;
SQL>
SQL>
SQL> select id, student_name, '語文' subject, chinese score from score_2 union
2 select id, student_name, '數學' subject, math score from score_2 union
3 select id, student_name, '英語' subject, english score from score_2
4 order by id;
ID STUDENT_NAME SUBJECT SCORE
---------- --------------- ---------- ----------
1 paul 數學 80
1 paul 英語 90
1 paul 語文 70
2 bob 數學 90
2 bob 英語 100
2 bob 語文 80
已選擇6行。
已用時間: 00: 00: 00.00
可以看到我們利用了SQL里面的union，先提取出所有語文科目的記錄，再連接上所有的數學、英語科目的記錄，最后按ID排序就可以了。下面我們來看另外一種實現方式，我們使用前面第一節創建的score表作為這次的插入目標表：
SQL> set timing on;
SQL> truncate table score;
表已截掉。
已用時間: 00: 00: 00.01
SQL>
SQL> insert all
2 into score values(id, student_name, '語文', chinese)
3 into score values(id, student_name, '數學', math)
4 into score values(id, student_name, '英語', english)
5 select id, student_name, chinese, math, english from score_2;
已創建6行。
已用時間: 00: 00: 00.00
SQL>
SQL> commit;
提交完成。
已用時間: 00: 00: 00.00
SQL>
SQL> select * from score;
ID STUDENT_NAME SUBJECT SCORE
---------- --------------- ---------- ----------
1 paul 語文 70
2 bob 語文 80
1 paul 數學 80
2 bob 數學 90
1 paul 英語 90
2 bob 英語 100
已選擇6行。
已用時間: 00: 00: 00.00
insert all into ... select是oracle9i里面提供的一個新功能，用于一次性批量執行多個插入語句，以提高效率。綠色高亮的第二處select語句首先查詢出各個科目的成績，然后在insert all into語句中引用到了這些查詢到的字段的值。
該SQL語句每次從score_2表中取出一條記錄，然后分別取出其chinese, math, english字段創建一條記錄。依此類推。
三、利用rownum關鍵字進行行列轉換：
關于如何使用rownum進行行列轉換，請參考本人之前在CSDN的Blog中所寫的一篇文章：利用Oracle rownum完成行列轉換一例
-------------------------------------------------------------
生活就像打牌，不是要抓一手好牌，而是要盡力打好一手爛牌。
posted on 2008-06-15 22:17
Paul Lin 閱讀(6155)
評論(2) 編輯 收藏 所屬分類:
Oracle 開發