SQL 行轉列（Pivot）實作範例：將水果統計資料由列轉為欄位顯示

需求情境

原始資料表 stat 記錄各班級擁有的水果數量，每種水果佔一列：

class   fruit   amount
------  ------  ------
class a apple   30
class a pear    15
class b apple   40
class b pear    20

需轉換為以班級為列、各水果數量為欄位的報表格式：

class   apple_amount  pear_amount
------  ------------  -----------
class a 30            15
class b 40            20

Oracle 語法實作（使用 DECODE）

SELECT class,
       SUM(apple_amount) AS apple_amount,
       SUM(pear_amount)  AS pear_amount
FROM (
    SELECT class,
           DECODE(fruit, 'apple', amount, 0) AS apple_amount,
           DECODE(fruit, 'pear',  amount, 0) AS pear_amount
    FROM stat
)
GROUP BY class;

建表與測試資料腳本

CREATE TABLE stat (
    class VARCHAR2(30),
    fruit VARCHAR2(30),
    amount NUMBER(10)
);

INSERT INTO stat VALUES ('class a', 'apple', 30);
INSERT INTO stat VALUES ('class a', 'pear',  15);
INSERT INTO stat VALUES ('class b', 'apple', 40);
INSERT INTO stat VALUES ('class b', 'pear',  20);
COMMIT;

SQL Server 等效寫法（CASE WHEN / PIVOT）

方法一：CASE WHEN 聚合（相容所有版本）

SELECT class,
       SUM(CASE WHEN fruit = 'apple' THEN amount ELSE 0 END) AS apple_amount,
       SUM(CASE WHEN fruit = 'pear'  THEN amount ELSE 0 END) AS pear_amount
FROM stat
GROUP BY class;

方法二：PIVOT 運算子（SQL Server 2005+）

SELECT class, [apple] AS apple_amount, [pear] AS pear_amount
FROM stat
PIVOT (
    SUM(amount) FOR fruit IN ([apple], [pear])
) AS pvt;

動態行轉列（未知欄位值）

若水果種類不固定，需動態產生 PIVOT 欄位清單：

DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);
SELECT @cols = STRING_AGG(QUOTENAME(fruit), ',') FROM (SELECT DISTINCT fruit FROM stat) AS t;
SET @sql = N'SELECT class, ' + @cols + N' FROM stat PIVOT (SUM(amount) FOR fruit IN (' + @cols + N')) AS p;';
EXEC sp_executesql @sql;

註：SQL Server 2017+ 可用 STRING_AGG，舊版需用 FOR XML PATH 串接。

總結

行轉列核心邏輯為「條件聚合」或「PIVOT 運算子」。Oracle 使用 DECODE 或 CASE，SQL Server 建議使用標準 CASE WHEN 或 PIVOT，並注意動態欄位處理。