Oracle 行列轉換完整技術指南：固定欄位、動態欄位與交叉表轉置

前言

行列轉換（Pivot/Unpivot）是報表開發常見需求。Oracle 11g+ 內建 PIVOT/UNPIVOT 語法，但舊版本或複雜動態欄位仍需用 DECODE/CASE、分析函數或 PL/SQL 實作。本文分三類場景完整示範。

一、固定欄位數：DECODE/CASE 聚合法

來源資料

STUDENT  SUBJECT  GRADE
-------- -------- ------
student1 語文      80
student1 數學      70
student1 英語      60
student2 語文      90
student2 數學      80
student2 英語     100

目標格式

STUDENT  語文  數學  英語
-------- ---- ---- ----
student1   80   70   60
student2   90   80  100

SQL 語法

select student,
  sum(decode(subject, '語文', grade)) "語文",
  sum(decode(subject, '數學', grade)) "數學",
  sum(decode(subject, '英語', grade)) "英語"
from score_table
group by student;

11g+ 可用 PIVOT：

select * from (
  select student, subject, grade from score_table
)
pivot (max(grade) for subject in ('語文' as "語文", '數學' as "數學", '英語' as "英語"));

二、不固定欄位：列轉行（字串串接）

來源資料

C1  C2
--- ---------
1   我
1   是
1   誰
2   知
2   道
3   不

目標格式

1  我是誰
2  知道
3  不

方法 1：PL/SQL 函數串接

create or replace function get_c2(p_c1 number) return varchar2 is
  v_result varchar2(4000);
begin
  for rec in (select c2 from t where c1 = p_c1 order by c2) loop
    v_result := v_result || rec.c2;
  end loop;
  return v_result;
end;
/

select distinct c1, get_c2(c1) c2 from t;

方法 2：SYS_CONNECT_BY_PATH + 分析函數（純 SQL）

select c1,
  substr(max(sys_connect_by_path(c2, ';')), 2) name
from (
  select c1, c2,
    row_number() over (order by c2) rn,
    lead(row_number() over (order by c2)) over (partition by c1 order by c2) rn1
  from t
)
start with rn1 is null
connect by rn1 = prior rn
group by c1;

方法 3：LISTAGG（11gR2+ 推薦）

select c1, listagg(c2, '') within group (order by c2) c2
from t
group by c1;

三、交叉表行列轉置（動態欄位 PIVOT）

來源資料

CLASS1  CALLDATE   CALLCOUNT
------  ---------- ----------
1       2005-08-08 40
1       2005-08-07 6
2       2005-08-08 77
3       2005-08-09 33
3       2005-08-08 9
3       2005-08-07 21

目標格式

CALLDATE   CALLCNT1 CALLCNT2 CALLCNT3
---------- -------- -------- --------
2005-08-09 0        0        33
2005-08-08 40       77       9
2005-08-07 6        0        21

動態 SQL 方案（Ref Cursor 回傳）

-- 1. 建立 Ref Cursor 型別套件
create or replace package pkg_getrecord is
  type myrctype is ref cursor;
end;
/

-- 2. 動態 PIVOT 函數
create or replace function fn_rs return pkg_getrecord.myrctype is
  v_sql varchar2(4000);
  v_cur pkg_getrecord.myrctype;
  cursor c1 is
    select ',sum(case when class1=' || class1 || ' then callcount else 0 end) "CallCount' || class1 || '"' col
    from t group by class1;
begin
  v_sql := 'select calldate ';
  for rec in c1 loop
    v_sql := v_sql || rec.col;
  end loop;
  v_sql := v_sql || ' from t group by calldate order by calldate desc';
  open v_cur for v_sql;
  return v_cur;
end;
/

-- 3. SQL*Plus 測試
var results refcursor;
exec :results := fn_rs;
print results;

四、DECODE 函數補充說明

DECODE(value, if1, then1, if2, then2, ..., else)
-- 等同於 CASE WHEN value=if1 THEN then1 WHEN value=if2 THEN then2 ... ELSE else END

-- 取兩值中較小者
select decode(sign(a-b), -1, a, b) from dual;
-- sign() 回傳：正數=1, 0=0, 負數=-1

五、版本選擇建議