Oracle 動態交叉表 (Pivot) 生成技術：靜態 SQL、動態 PL/SQL 與 Java 動態構建三種實現方案

業務場景

學生成績管理：規範化設計下，成績表 CJ(XH, XQ, KM, CJ) 縱向儲存，報表需橫向顯示「學號、姓名、語文、數學、英語...」。科目增減不應修改表結構。

方案一：靜態 SQL (科目固定、數量少)

SELECT JBXX.XH, JBXX.XM,
       (SELECT CJ FROM CJ WHERE XH=JBXX.XH AND XQ=1 AND KM=1) AS KM1,
       (SELECT CJ FROM CJ WHERE XH=JBXX.XH AND XQ=1 AND KM=2) AS KM2,
       (SELECT CJ FROM CJ WHERE XH=JBXX.XH AND XQ=1 AND KM=3) AS KM3
FROM JBXX
WHERE <班級條件>
ORDER BY JBXX.XH;

缺點：科目變更需改 SQL，不適合動態業務。

方案二：PL/SQL 動態 SQL 回傳 SYS_REFCURSOR

CREATE OR REPLACE PACKAGE PKG_PIVOT AS
  TYPE RC IS REF CURSOR;
  FUNCTION GET_SCORE_PIVOT(P_XQ NUMBER, P_CLASS VARCHAR2) RETURN RC;
END;
/

CREATE OR REPLACE PACKAGE BODY PKG_PIVOT AS
  FUNCTION GET_SCORE_PIVOT(P_XQ NUMBER, P_CLASS VARCHAR2) RETURN RC IS
    V_SQL   VARCHAR2(32767);
    V_RC    RC;
  BEGIN
    -- 1. 動態取得本學期開設科目
    FOR R IN (SELECT KM, MC FROM KMDM WHERE KM IN (SELECT DISTINCT KM FROM CJ WHERE XQ=P_XQ)) LOOP
      V_SQL := V_SQL || ', (SELECT CJ FROM CJ WHERE XH=JBXX.XH AND XQ='||P_XQ||' AND KM='||R.KM||') AS '||R.MC;
    END LOOP;

    V_SQL := 'SELECT JBXX.XH, JBXX.XM ' || V_SQL ||
             ' FROM JBXX WHERE CLASS_ID='''||P_CLASS||''' ORDER BY JBXX.XH';

    OPEN V_RC FOR V_SQL;
    RETURN V_RC;
  END;
END;
/

方案三：Java 應用層動態構建 (最大靈活性)

public ResultSet getPivotScore(Connection conn, int term, String classId) throws SQLException {
  StringBuilder sql = new StringBuilder("SELECT JBXX.XH, JBXX.XM");
  List subjects = new ArrayList<>();

  // 1. 查詢本學期開設科目
  try (Statement stmt = conn.createStatement();
       ResultSet rs = stmt.executeQuery(
         "SELECT KM, MC FROM KMDM WHERE KM IN (SELECT DISTINCT KM FROM CJ WHERE XQ=" + term + ")")) {
    while (rs.next()) {
      subjects.add(rs.getString("MC"));
      sql.append(", (SELECT CJ FROM CJ WHERE XH=JBXX.XH AND XQ=").append(term)
         .append(" AND KM=").append(rs.getInt("KM")).append(") AS ").append(rs.getString("MC"));
    }
  }

  sql.append(" FROM JBXX WHERE CLASS_ID=? ORDER BY JBXX.XH");

  // 2. 執行動態 SQL
  try (PreparedStatement pstmt = conn.prepareStatement(sql.toString())) {
    pstmt.setString(1, classId);
    return pstmt.executeQuery();  // 呼叫端負責關閉
  }
}

關鍵技術點對比

進階優化：條件聚合 (單掃描)

SELECT XH,
       SUM(DECODE(KM,1,CJ)) AS CHINESE,
       SUM(DECODE(KM,2,CJ)) AS MATH,
       SUM(DECODE(KM,3,CJ)) AS ENGLISH
FROM CJ
WHERE XQ=1
GROUP BY XH;

配合方案二/三動態生成 DECODE 列表，單次全表掃描完成樞紐，效能遠優於多子查詢。

最佳實踐建議

• 科目相對固定 → 靜態 SQL + 條件聚合
• 科目頻繁變更、多系統共用 → PL/SQL 封裝 + 條件聚合
• 前端需高度客製化 (可見欄位、排序、過濾) → Java 動態構建
• 大數據量 → 必須使用條件聚合，避免 N+1 子查詢
• 11g+ 可用 PIVOT / UNPIVOT 原生語法簡化寫法