您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

Oracle 列轉行與行轉列完整實作：SYS_CONNECT_BY_PATH、LISTAGG、PIVOT/UNPIVOT

編輯：Oracle 資料庫基礎

常見場景

報表開發中常需將多行資料合併為單一欄位（列轉行，如：員工技能合併顯示），或將單欄位拆解為多欄（行轉列，如：月份銷售額轉為 12 欄）。本文提供三種主流做法與效能比較。

測試資料準備

CREATE TABLE test_skills (
  emp_id   NUMBER,
  skill    VARCHAR2(100)
);

INSERT INTO test_skills VALUES (1, 'Oracle');
INSERT INTO test_skills VALUES (1, 'PL/SQL');
INSERT INTO test_skills VALUES (1, 'Linux');
INSERT INTO test_skills VALUES (2, 'Java');
INSERT INTO test_skills VALUES (2, 'Spring');
INSERT INTO test_skills VALUES (3, 'Python');
COMMIT;

方法一：SYS_CONNECT_BY_PATH（11g 以前相容）

SELECT emp_id,
       MAX(SUBSTR(SYS_CONNECT_BY_PATH(skill, ','), 2)) AS skills
FROM (
  SELECT emp_id, skill,
         ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY skill) AS rn,
         COUNT(*) OVER (PARTITION BY emp_id) AS cnt
  FROM test_skills
)
WHERE rn = cnt
START WITH rn = 1
CONNECT BY PRIOR emp_id = emp_id AND PRIOR rn = rn - 1
GROUP BY emp_id;

結果：

EMP_ID  SKILLS
------  --------------------
1       Linux,Oracle,PL/SQL
2       Java,Spring
3       Python

缺點：語法複雜、遞迴深度受限、大量資料效能差。

方法二：LISTAGG（11g R2+ 推薦）

SELECT emp_id,
       LISTAGG(skill, ',') WITHIN GROUP (ORDER BY skill) AS skills
FROM test_skills
GROUP BY emp_id;

優點：語法簡潔、內部最佳化、支援 ON OVERFLOW TRUNCATE（12c+）防止超過 4000 字元報錯。

方法三：XMLAGG（通用、無長度限制）

SELECT emp_id,
       RTRIM(XMLAGG(XMLELEMENT(e, skill, ',').EXTRACT('//text()') ORDER BY skill).GETCLOBVAL(), ',') AS skills
FROM test_skills
GROUP BY emp_id;

回傳 CLOB，適合超長字串。

行轉列：PIVOT（11g+）

測試資料

CREATE TABLE monthly_sales (
  dept_id   NUMBER,
  sale_month VARCHAR2(6),  -- YYYYMM
  amount    NUMBER
);
INSERT INTO monthly_sales VALUES (10, '202401', 1000);
INSERT INTO monthly_sales VALUES (10, '202402', 1200);
INSERT INTO monthly_sales VALUES (20, '202401', 800);
COMMIT;

PIVOT 語法

SELECT *
FROM monthly_sales
PIVOT (
  SUM(amount) FOR sale_month IN ('202401' AS m202401, '202402' AS m202402)
);

結果：

DEPT_ID  M202401  M202402
-------  -------  -------
10       1000     1200
20       800      (null)

動態列轉行/行轉列（PL/SQL 動態 SQL）

若欄位不固定（如不知有多少月份），需動態產生 SQL：

CREATE OR REPLACE FUNCTION pivot_sales(p_dept_id NUMBER) RETURN SYS_REFCURSOR IS
  v_sql   CLOB;
  v_cols  CLOB;
  v_cur   SYS_REFCURSOR;
BEGIN
  SELECT LISTAGG('''' || sale_month || ''' AS m' || sale_month, ',') WITHIN GROUP (ORDER BY sale_month)
  INTO   v_cols
  FROM (SELECT DISTINCT sale_month FROM monthly_sales WHERE dept_id = p_dept_id);

  v_sql := 'SELECT * FROM monthly_sales ' ||
           'PIVOT (SUM(amount) FOR sale_month IN (' || v_cols || ')) ' ||
           'WHERE dept_id = ' || p_dept_id;

  OPEN v_cur FOR v_sql;
  RETURN v_cur;
END;

效能比較與選型建議

方法	適用版本	效能	長度限制	建議場景
LISTAGG	11gR2+	最佳	4000 字元（VARCHAR2）	一般報表、字串聚合
LISTAGG + ON OVERFLOW TRUNCATE	12c+	最佳	可截斷並加尾碼	不確定長度、需避免報錯
XMLAGG	9i+	良	無（CLOB）	超長字串、舊版相容
SYS_CONNECT_BY_PATH	9i+	差	4000 字元	極舊版本、特殊排序需求
PIVOT/UNPIVOT	11g+	良	依欄位數	固定欄位行轉列