如何使用PIVOT函數在SQL中動態產生欄位？

使用 PIVOT 函數在 SQL 中動態產生欄位

本文解決了在 SQL 中動態建立列來表示客戶獎勵資料庫中不同獎勵類型的挑戰。 獎勵類型並不固定，可能會隨著時間而改變。 我們將探索使用 SQL PIVOT 函數的解決方案，演示靜態和動態方法，並包括添加總計列以獲取綜合結果。

問題：

我們有客戶、他們的獎勵、獎勵類型的資料庫表。 獎勵的類型是動態的；它們可以被添加或刪除。 目標是產生一個結果集，其中包含每種獎勵類型的一列，顯示每個客戶的該獎勵類型的計數。

靜態解（獎勵類型固定數量）：

如果事先知道獎勵類型，一個簡單的 PIVOT 查詢就足夠了：

SELECT name, [Bronze], [Silver], [Gold], [Platinum], [AnotherOne]
FROM
(
  SELECT c.name, cr.description, r.typeid
  FROM customers c
  LEFT JOIN rewards r ON c.id = r.customerid
  LEFT JOIN customerrewards cr ON r.typeid = cr.typeid
) x
PIVOT
(
  COUNT(typeid)
  FOR description IN ([Bronze], [Silver], [Gold], [Platinum], [AnotherOne])
) p;

此查詢效果很好，但前提是獎勵類型（「青銅」、「白銀」等）已預先定義。

動態解（獎勵類型數量未知）：

對於動態數量的獎勵類型，我們需要動態 SQL：

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SELECT @cols = STUFF((SELECT ',' + QUOTENAME(description)
                    FROM customerrewards
                    GROUP BY description, typeid
                    ORDER BY typeid
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'');

SET @query = 'SELECT name,' + @cols + ' FROM 
             (
                SELECT c.name, cr.description, r.typeid
                FROM customers c
                LEFT JOIN rewards r ON c.id = r.customerid
                LEFT JOIN customerrewards cr ON r.typeid = cr.typeid
            ) x
            PIVOT 
            (
                COUNT(typeid)
                FOR description IN (' + @cols + ')
            ) p;';

EXECUTE(@query);

此程式碼動態建立 SQL 查詢，從 customerrewards 表格建立列清單。 這允許適應任意數量的獎勵類型。

新增總計欄位：

要包含每位客戶的獎勵總數，我們可以使用 ROLLUP 函數（靜態和動態版本如下所示）：

靜態版本總計：

SELECT name, SUM([Bronze]) Bronze, SUM([Silver]) Silver, 
  SUM([Gold]) Gold, SUM([Platinum]) Platinum, SUM([AnotherOne]) AnotherOne
FROM 
(
  SELECT name, [Bronze], [Silver], [Gold], [Platinum], [AnotherOne]
  FROM
  (
    SELECT c.name, cr.description, r.typeid
    FROM customers c
    LEFT JOIN rewards r ON c.id = r.customerid
    LEFT JOIN customerrewards cr ON r.typeid = cr.typeid
  ) x
  PIVOT
  (
    COUNT(typeid)
    FOR description IN ([Bronze], [Silver], [Gold], [Platinum], [AnotherOne])
  ) p
) x
GROUP BY name WITH ROLLUP;

動態版本總計：

DECLARE @cols AS NVARCHAR(MAX),
    @colsRollup AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SELECT @cols = STUFF((SELECT ',' + QUOTENAME(description) 
                    FROM customerrewards
                    GROUP BY description, typeid
                    ORDER BY typeid
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');

SELECT @colsRollup 
      = STUFF((SELECT ', SUM(' + QUOTENAME(description) + ') AS ' + QUOTENAME(description)
                    FROM customerrewards
                    GROUP BY description, typeid
                    ORDER BY typeid
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'');


SET @query 
          = 'SELECT name, ' + @colsRollup + '
             FROM
             (
                SELECT name,' + @cols + ' FROM 
                 (
                    SELECT c.name, cr.description, r.typeid
                    FROM customers c
                    LEFT JOIN rewards r ON c.id = r.customerid
                    LEFT JOIN customerrewards cr ON r.typeid = cr.typeid
                ) x
                PIVOT 
                (
                    COUNT(typeid)
                    FOR description IN (' + @cols + ')
                ) p 
              ) x1
              GROUP BY name WITH ROLLUP;';

EXECUTE(@query);

這些動態 SQL 範例提供了強大且靈活的解決方案，用於產生具有可變數量獎勵類型列（包括有用的總計列）的報告。 請記住調整表格和列名稱以符合您的特定資料庫架構。

以上是如何使用PIVOT函數在SQL中動態產生欄位？的詳細內容。更多資訊請關注PHP中文網其他相關文章！