SQL Server 2005 進階技巧：審計系統、PIVOT/UNPIVOT 交叉表、XML 拆分合併欄位

1. 內建工具建立審計系統

追蹤誰訪問資料、如何核准權限、監控異常登入/操作，防範內部威脅。

2. PIVOT / UNPIVOT 行列轉換

DECLARE @t TABLE (日期 DATETIME, 時間 VARCHAR(20), 售貨金額 INT);
INSERT @t VALUES 
('2006-01-02','早上',50),('2006-01-02','中午',20),('2006-01-02','晚上',30),('2006-01-02','零晨',40),
('2006-01-03','早上',40),('2006-01-03','中午',60),('2006-01-03','晚上',50),('2006-01-03','零晨',50),
('2006-01-04','早上',80),('2006-01-04','中午',60),('2006-01-04','晚上',20),('2006-01-04','零晨',40);

-- 靜態列
SELECT *, 金額小計=(SELECT SUM(售貨金額) FROM @t WHERE 日期=PT.日期)
FROM @t AS TAB
PIVOT (MAX(售貨金額) FOR 時間 IN ([早上],[中午],[晚上],[零晨])) AS PT;

-- 動態列（列不固定）
DECLARE @S VARCHAR(MAX) = '';
SELECT @S = @S + ',[' + 時間 + ']' FROM @t GROUP BY 時間;
SET @S = STUFF(@S,1,1,'');
EXEC('
SELECT 日期,' + @S + ',金額小計=(SELECT SUM(售貨金額) FROM @t WHERE 日期=PT.日期)
FROM @t AS TAB
PIVOT (MAX(售貨金額) FOR 時間 IN (' + @S + ')) AS PT
');

3. XML 拆分欄位值（逗號分隔 → 多行）

DECLARE @t TABLE (id INT, [values] VARCHAR(100));
INSERT @t VALUES (1,'aa,bb'),(2,'aaa,bbb,ccc');

SELECT A.id, B.value
FROM (
    SELECT id, [values] = CONVERT(XML, '' + REPLACE([values], ',', '') + '')
    FROM @t
) A
OUTER APPLY (
    SELECT value = N.v.value('.', 'VARCHAR(100)')
    FROM A.[values].nodes('/ROOT/V') N(v)
) B;
-- 結果：1 aa / 1 bb / 2 aaa / 2 bbb / 2 ccc

4. XML 合併欄位值（多行 → 逗號分隔）

DECLARE @t TABLE (id INT, value VARCHAR(10));
INSERT @t VALUES (1,'aa'),(1,'bb'),(2,'aaa'),(2,'bbb'),(2,'ccc');

SELECT DISTINCT id,
       STUFF((SELECT ',' + value FROM @t WHERE id = T.id FOR XML PATH('')), 1, 1, '') AS merged
FROM @t T;
-- 結果：1 aa,bb / 2 aaa,bbb,ccc

這些技巧在報表開發、資料清洗、ETL 轉換中極為實用，避免了游標或複雜字串操作，效能與可讀性俱佳。