SQL Server 與 Oracle CASE / DECODE 行轉列（樞紐）完整對比與實戰範例

範例資料：成績表 Results

一、簡單值映射：DECODE vs CASE

Oracle DECODE

SELECT name,
       DECODE(subject, '語文', 1, '數學', 2, '英語', 3) AS subject_code
FROM results;

Oracle / SQL Server 通用 CASE

-- 簡單 CASE
SELECT name,
       CASE subject
         WHEN '語文' THEN 1
         WHEN '數學' THEN 2
         WHEN '英語' THEN 3
       END AS subject_code
FROM results;

-- 搜尋 CASE（更靈活，支援複合條件）
SELECT name,
       CASE
         WHEN subject = '語文' THEN 1
         WHEN subject = '數學' THEN 2
         WHEN subject = '英語' THEN 3
       END AS subject_code
FROM results;

結論：DECODE 為 Oracle 專有，簡潔但功能受限；CASE 為 ANSI 標準，跨平台、支援複雜邏輯、可巢狀，**推薦統一使用 CASE**。

二、行轉列（樞紐分析）：聚合 + CASE/DECODE

Oracle DECODE 寫法

SELECT name,
       SUM(DECODE(subject, '語文', result, 0)) AS 語文,
       SUM(DECODE(subject, '數學', result, 0)) AS 數學,
       SUM(DECODE(subject, '英語', result, 0)) AS 英語
FROM results
GROUP BY name;

標準 CASE 寫法（跨平台）

SELECT name,
       SUM(CASE WHEN subject = '語文' THEN result ELSE 0 END) AS 語文,
       SUM(CASE WHEN subject = '數學' THEN result ELSE 0 END) AS 數學,
       SUM(CASE WHEN subject = '英語' THEN result ELSE 0 END) AS 英語
FROM results
GROUP BY name;

結果集

三、中間過程展示（不聚合）

-- Oracle
SELECT name,
       DECODE(subject, '語文', result, 0) AS 語文,
       DECODE(subject, '數學', result, 0) AS 數學,
       DECODE(subject, '英語', result, 0) AS 英語
FROM results;

-- 標準 CASE
SELECT name,
       CASE WHEN subject = '語文' THEN result ELSE 0 END AS 語文,
       CASE WHEN subject = '數學' THEN result ELSE 0 END AS 數學,
       CASE WHEN subject = '英語' THEN result ELSE 0 END AS 英語
FROM results;

中間結果每列僅有一科有分數，其餘為 0，再經 SUM + GROUP BY 合併。

四、進階：動態行轉列（未知科目）

靜態 SQL 無法動態產生欄，需動態 SQL 或應用層組裝：

-- Oracle 11g+ PIVOT 语法（靜態）
SELECT * FROM (
  SELECT name, subject, result FROM results
)
PIVOT (
  SUM(result) FOR subject IN ('語文' AS 語文, '數學' AS 數學, '英語' AS 英語)
);

-- 動態 PL/SQL 產生 PIVOT SQL
DECLARE
  v_sql VARCHAR2(4000);
  v_cols VARCHAR2(4000);
BEGIN
  SELECT LISTAGG('''' || subject || ''' AS ' || subject, ',') WITHIN GROUP (ORDER BY subject)
  INTO v_cols FROM (SELECT DISTINCT subject FROM results);
  v_sql := 'SELECT * FROM (SELECT name, subject, result FROM results) PIVOT (SUM(result) FOR subject IN (' || v_cols || '))';
  EXECUTE IMMEDIATE v_sql;
END;

五、效能與最佳實踐

六、SQL Server 專用：PIVOT 運算子

SELECT name, [語文], [數學], [英語]
FROM (
  SELECT name, subject, result FROM results
) src
PIVOT (
  SUM(result) FOR subject IN ([語文], [數學], [英語])
) pvt;