Oracle開發專題之：行列轉換__Oracle

一、使用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