SQL Server CASE 運算式完整應用指南：簡單/搜尋形式、搭配 SUM 實現行列轉換、分組抬頭抑制

語法兩種形式

-- 簡單 CASE：僅做等值比較
CASE input_expression
    WHEN when_expression THEN result_expression
    [...]
    ELSE else_result_expression
END

-- 搜尋 CASE：支援任意布林運算式
CASE
    WHEN boolean_expression THEN result_expression
    [...]
    ELSE else_result_expression
END

案例 A：簡單 CASE 做資料美化 (pubs 資料庫)

SELECT 
    Category = CASE type
        WHEN 'popular_comp' THEN 'Popular Computing'
        WHEN 'mod_cook'     THEN 'Modern Cooking'
        WHEN 'business'     THEN 'Business'
        WHEN 'psychology'   THEN 'Psychology'
        WHEN 'trad_cook'    THEN 'Traditional Cooking'
        ELSE 'Not yet categorized'
    END,
    CAST(title AS varchar(25)) AS 'Shortened Title',
    price
FROM titles
WHERE price IS NOT NULL
ORDER BY type, price;

案例 B：搜尋 CASE + SUBSTRING 做代碼解碼

SELECT 
    SUBSTRING(RTRIM(a.au_fname) + ' ' + RTRIM(a.au_lname), 1, 25) AS Name,
    a.au_id, ta.title_id,
    Type = CASE
        WHEN SUBSTRING(ta.title_id, 1, 2) = 'BU' THEN 'Business'
        WHEN SUBSTRING(ta.title_id, 1, 2) = 'MC' THEN 'Modern Cooking'
        WHEN SUBSTRING(ta.title_id, 1, 2) = 'PC' THEN 'Popular Computing'
        WHEN SUBSTRING(ta.title_id, 1, 2) = 'PS' THEN 'Psychology'
        WHEN SUBSTRING(ta.title_id, 1, 2) = 'TC' THEN 'Traditional Cooking'
    END
FROM titleauthor ta JOIN authors a ON ta.au_id = a.au_id;

案例 C：CASE + SUM 實現「已知有限類別」的行列轉換 (樞紐)

-- 原始資料：ItemId, Category, PerfLevel(1/2/3)
SELECT 
    Category,
    COUNT(*) AS TotalCnt,
    SUM(CASE PerfLevel WHEN 1 THEN 1 ELSE 0 END) AS Perf1_Cnt,
    SUM(CASE PerfLevel WHEN 2 THEN 1 ELSE 0 END) AS Perf2_Cnt,
    SUM(CASE PerfLevel WHEN 3 THEN 1 ELSE 0 END) AS Perf3_Cnt
FROM Items
GROUP BY Category;

結果：每類別一列，各等級計數橫向展開。類別值**固定已知**時極佳；動態類別需動態 SQL。

案例 D：分組抬頭抑制 (Group Header Suppression) — 報表常用

-- 目標：同一 dwid 只顯示第一筆的單位名稱
SELECT 
    CASE WHEN (SELECT COUNT(id) FROM cj WHERE dwid = b.dwid AND id <= b.id) = 1 
         THEN a.name ELSE '' END AS UnitName,
    b.name AS EmpName, b.cj AS Score
FROM dw a LEFT JOIN cj b ON a.dwid = b.dwid
ORDER BY b.dwid, b.id;

邏輯：子查詢計算「同群組中排序鍵 ≤ 當前列」的筆數，等於 1 表示該群組第一筆，顯示名稱；其餘顯示空字串。同樣適用於 SSRS/Crystal Reports 自訂程式碼。

進階提示：
• CASE 可嵌套，但建議改用 IIF() (SQL 2012+) 或 CHOOSE() 簡化簡單邏輯。
• ELSE NULL 可省略，隱含回傳 NULL。
• 行列轉換現代寫法請用 PIVOT / UNPIVOT，但 CASE+SUM 相容性最廣、效能穩定。