ADO.NET 實作動態交叉表報表：動態 SQL 與 DataSet 關聯式轉換比較

動態交叉表 (Crosstab/Pivot) 報表的兩種核心實作架構

需求：書店銷售報表，列為作者，欄為書店 (每月新增/關閉)，值為銷售額彙總。欄位數量動態變化。

架構一：預存程序 + 動態 SQL (傳統 T-SQL 方案)

CREATE PROC usp_SalesCrosstab AS
BEGIN
    DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);
    -- 1. 動態產生欄位清單 (CASE 運算式)
    SELECT @cols = COALESCE(@cols + ',', '') + 
        'SUM(CASE WHEN Store = ' + QUOTENAME(Store, '''') + ' THEN Sales ELSE 0 END) AS ' + QUOTENAME(Store)
    FROM (SELECT DISTINCT Store FROM vwSales2) AS S;

    -- 2. 組裝完整查詢
    SET @sql = 'SELECT Author, ' + @cols + ' FROM vwSales2 GROUP BY Author ORDER BY Author;';

    -- 3. 執行
    EXEC sp_executesql @sql;
END

• 優點：邏輯在資料庫層完成，網路傳輸僅最終結果集，適用大量資料；相容所有用戶端 (不需 ADO.NET 特定功能)。
• 缺點：動態 SQL 難除錯、計畫快取壓力、SQL 注入風險 (需 `QUOTENAME`)、`CASE` 運算式在極多欄位時效能下降、維護複雜。

架構二：ADO.NET DataSet 關聯式記憶體轉換 (中介層方案)

// 1. 取得扁平資料 (Author, Store, Sales)
DataTable dtFlat = GetFlatSalesData(); 

// 2. 建立 DataSet 關聯 (可選，單表亦可)
DataSet ds = new DataSet();
ds.Tables.Add(dtFlat);

// 3. 使用 DataView / LINQ / 自訂演算法 樞轉
var pivot = dtFlat.AsEnumerable()
    .GroupBy(r => r.Field("Author"))
    .Select(g => new {
        Author = g.Key,
        Stores = g.ToDictionary(r => r.Field("Store"), r => r.Field("Sales"))
    }).ToList();

// 4. 動態建立報表 DataTable (供 DataGrid/GridView 繫結)
DataTable dtReport = new DataTable();
dtReport.Columns.Add("Author");
foreach (var store in allStores) dtReport.Columns.Add(store, typeof(decimal));

foreach (var row in pivot) {
    var dr = dtReport.NewRow();
    dr["Author"] = row.Author;
    foreach (var kv in row.Stores) dr[kv.Key] = kv.Value;
    dtReport.Rows.Add(dr);
}

• 優點：類型安全、IntelliSense、易除錯、邏輯靈活 (可計算總計、百分比、條件格式)、資料庫只需提供扁平化視圖/查詢、支援離線/快取模式。
• 缺點：將所有明細資料拉至應用層記憶體，資料量大時 (百萬列+) 記憶體壓力大、網路頻寬佔用高；不適合極大資料集。

選型決策矩陣

現代替代方案

• SQL Server 2016+ 內建 PIVOT/UNPIVOT：靜態欄位語法簡潔，動態仍需動態 SQL。
• SSRS / Power BI / Excel Power Pivot：報表工具內建矩陣/樞紐分析，無需手寫程式碼。
• 前端框架 (React/Vue/Angular) + 聚合 API：後端提供彙總 JSON，前端元件渲染動態表格。