SQL Server 2005 Beta 2 Transact-SQL 增強功能（二）：PIVOT、UNPIVOT、APPLY 與錯誤處理

SQL Server 2005 Beta 2 Transact-SQL 增強功能（二）

PIVOT 運算子

PIVOT 運算子可將資料列轉為資料行，產生交叉分析報表。以 ItemAttributes 表為例，傳統寫法需用 CASE 搭配 MAX 聚合：

SELECT itemid,
    MAX(CASE WHEN attribute = 'artist' THEN value END) AS [artist],
    MAX(CASE WHEN attribute = 'name' THEN value END) AS [name],
    MAX(CASE WHEN attribute = 'type' THEN value END) AS [type],
    MAX(CASE WHEN attribute = 'height' THEN value END) AS [height],
    MAX(CASE WHEN attribute = 'width' THEN value END) AS [width]
FROM ItemAttributes AS ATR
WHERE itemid IN (5,6)
GROUP BY itemid

使用 PIVOT 簡化為：

SELECT * FROM ItemAttributes AS ATR
PIVOT ( MAX(value) FOR attribute IN ([artist], [name], [type], [height], [width]) ) AS PVT
WHERE itemid IN (5,6)

PIVOT 關鍵術語

• table_expression：PIVOT 作用的虛擬表（FROM 與 PIVOT 之間部分）
• pivot_column：要旋轉為結果欄的欄位（範例中 attribute）
• column_list：pivot_column 中要呈現為結果欄的值清單（IN 子句）
• aggregate_function：產生結果值的聚合函數（範例 MAX()）
• value_column：作為聚合函數參數的欄位（範例 value）
• group_by_list：隱含的分組欄位——table_expression 中除 pivot_column 和 value_column 外的所有欄位（範例 itemid）
• select_list：SELECT 後的欄位清單，可包含 group_by_list 和 column_list 欄位

關鍵洞見： group_by_list 是隱含的，未在查詢中顯式指定。理解此點是掌握 PIVOT 行為的關鍵。

PIVOT 進階範例

搭配 JOIN 使用（需用 CTE/衍生表避免重複欄位錯誤）

WITH PNT AS (
    SELECT ITM.*, ATR.attribute, ATR.value
    FROM AuctionItems AS ITM
    JOIN ItemAttributes AS ATR ON ITM.itemid = ATR.itemid
    WHERE ITM.itemtype = 'Painting'
)
SELECT * FROM PNT
PIVOT ( MAX(value) FOR attribute IN ([artist], [name], [type], [height], [width]) ) AS PVT

交叉分析報表：採購訂單運送方式統計

SELECT EmployeeID, [1] AS SM1, [2] AS SM2, [3] AS SM3, [4] AS SM4, [5] AS SM5
FROM (
    SELECT PurchaseOrderID, EmployeeID, ShipMethodID
    FROM Purchasing.PurchaseOrderHeader
) ORD
PIVOT ( COUNT(PurchaseOrderID) FOR ShipMethodID IN ([1],[2],[3],[4],[5]) ) AS PVT

動態樞軸值：以運算式結果為樞軸（訂單年份）

SELECT EmployeeID, [2001] AS Y2001, [2002] AS Y2002, [2003] AS Y2003, [2004] AS Y2004
FROM (
    SELECT EmployeeID, YEAR(OrderDate) AS OrderYear, Freight
    FROM Purchasing.PurchaseOrderHeader
) ORD
PIVOT ( SUM(Freight) FOR OrderYear IN ([2001],[2002],[2003],[2004]) ) AS PVT

處理 NULL：使用 ISNULL 替換不存在的交集

SELECT OrderYear,
    ISNULL([1],0) AS M01, ISNULL([2],0) AS M02, ..., ISNULL([12],0) AS M12
FROM (...) ORD
PIVOT ( SUM(Quantity) FOR OrderMonth IN ([1]...[12]) ) AS PVT

多欄位樞軸：串聯年份月份

SELECT * FROM (
    SELECT CustomerID, OrderYear*100+OrderMonth AS YM, Quantity
    FROM OrdersFact WHERE ...
) ORD
PIVOT ( SUM(Quantity) FOR YM IN ([200301],[200302],...,[200403]) ) AS PVT

UNPIVOT 運算子

UNPIVOT 將預先旋轉的資料標準化。語法與元素類似 PIVOT。

範例：將 AuctionItems 的 itemtype、whenmade、initialprice 轉為列：

SELECT itemid, attribute, value FROM (
    SELECT itemid,
        CAST(itemtype AS SQL_VARIANT) AS itemtype,
        CAST(whenmade AS SQL_VARIANT) AS whenmade,
        CAST(initialprice AS SQL_VARIANT) AS initialprice
    FROM AuctionItems
) ITM
UNPIVOT ( value FOR attribute IN ([itemtype], [whenmade], [initialprice]) ) AS UPV

限制： column_list 所有欄位必須相同資料類型、長度、精度。使用 SQL_VARIANT 可滿足此限制並保留原始類型。UNPIVOT 會移除 value 中的 NULL，非 PIVOT 嚴格逆運算。

APPLY 關聯運算子

APPLY 允許對外部表運算式每列調用指定資料表值函數一次。兩種形式：CROSS APPLY（僅回傳函數有結果的列）與 OUTER APPLY（函數無結果時仍回傳外部列，對應欄位為 NULL）。

CROSS APPLY 範例

CREATE FUNCTION dbo.fn_scalar_min_max(@p1 INT, @p2 INT) RETURNS TABLE AS
RETURN SELECT CASE WHEN @p1 < @p2 THEN @p1 ELSE @p2 END AS mn,
       CASE WHEN @p1 > @p2 THEN @p1 ELSE @p2 END AS mx

SELECT * FROM T1 CROSS APPLY fn_scalar_min_max(col1, col2) AS M

OUTER APPLY 範例

同樣查詢改用 OUTER APPLY，無經理的部門（Gardening）仍會出現在結果中，對應函數欄位為 NULL。

相關子查詢中的資料表值函數

SQL Server 2005 移除 SQL Server 2000 的限制，現可在相關子查詢中引用資料表值函數並傳入外部查詢欄位作參數。

SELECT * FROM Departments AS D
WHERE (SELECT COUNT(*) FROM fn_getsubtree(D.deptmgrid)) >= 3

新 DRI 參考動作：SET DEFAULT 與 SET NULL

SQL Server 2005 新增支援 ANSI SQL 定義的四種參考動作中的 SET DEFAULT 與 SET NULL（原支援 NO ACTION、CASCADE）。

• SET DEFAULT：刪除/更新被參考列時，將參考表對應列設為預設值
• SET NULL：設為 NULL（前提是允許 NULL）

範例：Orders 表外鍵定義 ON DELETE SET NULL ON UPDATE SET DEFAULT。刪除客戶 FRIDA → 訂單 customerid 變 NULL；更新客戶 BILLY → 訂單 customerid 變預設值 'DUMMY'。

效能與錯誤處理增強

BULK 資料列集提供者

OPENROWSET 新增 BULK 提供者，可存取關聯格式檔案資料。支援 FORMATFILE、SINGLE_BLOB/CLOB/NCLOB、CODEPAGE、FIRSTROW/LASTROW、ROWS_PER_BATCH、MAXERRORS、ERRORFILE（新增：格式錯誤列複製至此檔案，並自動建立 .ERROR.txt 控制檔記錄錯誤診斷）。

TRY...CATCH 異常處理

SQL Server 2005 引入 TRY...CATCH 結構，解決舊版需在每句後檢查錯誤、嚴重錯誤導致批次終止無法捕獲等問題。可捕獲嚴重度 < 21 的錯誤（不含連線中斷錯誤）。

BEGIN TRY
    INSERT INTO Employees(empid, empname, mgrid) VALUES(1, 'Emp1', NULL)
    PRINT 'After INSERT.'
END TRY
BEGIN CATCH
    PRINT 'INSERT failed.'
    -- 糾正動作
END CATCH

錯誤資訊函數（僅 CATCH 區塊可用）：

• ERROR_NUMBER()、ERROR_MESSAGE()、ERROR_SEVERITY()、ERROR_STATE()
• 可多次呼叫，值保持不變（優於 @@ERROR）

巢狀錯誤處理與 XACT_STATE()

未處理錯誤會向上傳遞至呼叫堆疊中最近的 TRY...CATCH。XACT_STATE() 回傳交易狀態：0（無交易）、1（可提交/回滾）、-1（不可提交狀態，嚴重度 ≥17 錯誤導致）。不可提交交易僅允許讀取、必須 ROLLBACK 終止。

BEGIN TRY
    BEGIN TRAN
    INSERT INTO Employees(empid, empname, mgrid) VALUES(3, 'Emp3', 1)
    COMMIT TRAN
END TRY
BEGIN CATCH
    IF XACT_STATE() = -1 ROLLBACK TRAN
    ELSE IF XACT_STATE() = 1 COMMIT TRAN -- 或 ROLLBACK
END CATCH

其他影響 Transact-SQL 的增強功能

TOP 增強

• 接受數值運算式（變數、子查詢），不限常數
• 支援 DELETE、UPDATE、INSERT
• 語法：TOP (expression) [PERCENT]，WITH TIES 僅 SELECT 支援

DECLARE @n BIGINT = 2
SELECT TOP(@n) * FROM Purchasing.PurchaseOrderHeader ORDER BY OrderDate DESC

-- 動態：每月平均訂單數的最新訂單
SELECT TOP(SELECT COUNT(*)/DATEDIFF(month, MIN(OrderDate), MAX(OrderDate)) 
    FROM Purchasing.PurchaseOrderHeader) * 
FROM Purchasing.PurchaseOrderHeader ORDER BY OrderDate DESC

-- 替代 SET ROWCOUNT 分批刪除
DELETE TOP(1000) FROM BigTable WHERE datetimecol < '20000101'
WHILE @@ROWCOUNT > 0 DELETE TOP(1000) FROM BigTable WHERE datetimecol < '20000101'

OUTPUT 子句（DML with Results）

INSERT/UPDATE/DELETE 可用 OUTPUT 子句將異動資料回傳至表格變數。語法：OUTPUT INTO @table_variable。適用清除歸檔、訊息處理等情境。

其他功能

• MAX 指定詞：VARCHAR(MAX)、NVARCHAR(MAX)、VARBINARY(MAX) 取代舊 TEXT/NTEXT/IMAGE
• XML/XQuery 原生支援
• DDL 觸發器、Service Broker、事件通知