您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL 動態行轉列兩大絕招：靜態 PIVOT vs 動態 SQL 拼接

編輯：關於SqlServer

場景

訂單表按 Url 分組，需按月份（1-12 月）橫向展示 Quality 彙總，列名不固定、需隨數據自適應。

方案一：靜態 PIVOT（列固定、已知範圍）

SELECT *
FROM (
    SELECT Url,
           CASE MONTH
               WHEN 1  THEN '1月'  WHEN 2  THEN '2月'  WHEN 3  THEN '3月'
               WHEN 4  THEN '4月'  WHEN 5  THEN '5月'  WHEN 6  THEN '6月'
               WHEN 7  THEN '7月'  WHEN 8  THEN '8月'  WHEN 9  THEN '9月'
               WHEN 10 THEN '10月' WHEN 11 THEN '11月' WHEN 12 THEN '12月'
           END AS Month,
           Quality
    FROM (
        SELECT b.Url, DATENAME(M, a.AuditingTime) AS Month, SUM(c.Quality) AS Quality
        FROM tb_order a
        LEFT JOIN tb_WebSiteInfo b ON a.WebSiteInfoID = b.ID
        LEFT JOIN tb_OrderList c ON c.OrderID = a.ID
        WHERE a.AuditingTime > '2013-01-01' AND b.ID > 0 AND a.Auditing = 2
        GROUP BY b.Url, DATENAME(M, a.AuditingTime)
    ) h
) hh
PIVOT ( SUM(Quality) FOR Month IN ([1月],[2月],[3月],[4月],[5月],[6月],
                                   [7月],[8月],[9月],[10月],[11月],[12月]) ) a;

優點：語法標準、可讀性強；缺點：列名硬編碼，新增月份需改代碼。

方案二：動態 SQL 拼接（列未知、完全自適應）

DECLARE @sql  VARCHAR(8000) = '',
        @sql2 VARCHAR(8000) = '';

-- 1. 先查出實際存在的月份列表（如 2013-01, 2013-02...）
SELECT @sql = ISNULL(@sql + ',', '') + '[' + CONVERT(VARCHAR(7), AuditingTime, 20) + ']'
FROM tb_order a
LEFT JOIN tb_WebSiteInfo b ON a.WebSiteInfoID = b.ID
LEFT JOIN tb_OrderList c ON c.OrderID = a.ID
WHERE a.AuditingTime > '2013-01-01' AND b.ID > 0 AND a.Auditing = 2
GROUP BY CONVERT(VARCHAR(7), AuditingTime, 20);

PRINT @sql;  -- 調試輸出：[2013-01],[2013-02],...

-- 2. 拼接完整 PIVOT 查詢
SET @sql2 = 'SELECT * FROM (
    SELECT Url, CONVERT(VARCHAR(7), AuditingTime, 20) AS AuditingTime, SUM(Quality) AS Quality
    FROM tb_order a
    LEFT JOIN tb_WebSiteInfo b ON a.WebSiteInfoID = b.ID
    LEFT JOIN tb_OrderList c ON c.OrderID = a.ID
    WHERE b.ID > 0 AND a.Auditing = 2
    GROUP BY Url, CONVERT(VARCHAR(7), AuditingTime, 20)
) hh PIVOT (SUM(Quality) FOR AuditingTime IN (' + @sql + ')) b';

PRINT @sql2;  -- 調試輸出完整 SQL
EXEC(@sql2);

核心技巧對比

維度	靜態 PIVOT	動態 SQL
適用場景	列固定、業務穩定（如固定 12 月、固定產品線）	列動態、用戶自定義維度、跨年跨月自適應
維護成本	低（改代碼即可）	中（需注意注入、長度溢出）
執行計劃復用	好（參數化後可復用）	差（每次動態編譯，可用 `sp_executesql` + 參數化緩解）
最大列數限制	受限於 PIVOT IN 列表長度	受限於 `NVARCHAR(MAX)` 2GB，實際受 `GROUP BY` 列數限制