實作動態 PIVOT 查詢

使用過 PIVOT 的朋友都知道,當你有 N 個值要轉為樞紐資料行,您在 IN 就要寫 N 次,您可以透過 Dynamic SQL 的方式來幫您組織 PIVOT 所需的指令碼。

情境說明

在 SQL Server 2000 要做到 Cross-Tab 或 Rotating Data,必須透過 GROUP BY 加上 CASE WHEN 或子查詢來完成,例如下列的指令碼:

SELECT P1.*, (P1.Q1 + P1.Q2 + P1.Q3 + P1.Q4) AS YearTotal
FROM (SELECT Year,
             SUM(CASE P.Quarter WHEN 1 THEN P.Amount ELSE 0 END) AS Q1,
             SUM(CASE P.Quarter WHEN 2 THEN P.Amount ELSE 0 END) AS Q2,
             SUM(CASE P.Quarter WHEN 3 THEN P.Amount ELSE 0 END) AS Q3,
             SUM(CASE P.Quarter WHEN 4 THEN P.Amount ELSE 0 END) AS Q4
     FROM Pivot AS P
     GROUP BY P.Year) AS P1
GO
SELECT YEAR,
       Q1= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 1 AND YEAR =
  Q.YEAR),0),
       Q2= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 2 AND YEAR =
  Q.YEAR),0),
       Q3= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 3 AND YEAR =
  Q.YEAR),0),
       Q4= ISNULL((SELECT AMOUNT FROM QTRSALES WHERE QUARTER = 4 AND YEAR =
  Q.YEAR),0)
     FROM QTRSALES Q
     GROUP BY YEAR

SQL Server 2005 開始新增 PIVOT 關係運算子,來幫助您更容易來達到上述功能,而且更加容易維護和理解,例如下列的指令碼:

SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days, 
[0], [1], [2], [3], [4]
FROM
(SELECT DaysToManufacture, StandardCost 
    FROM Production.Product) AS SourceTable
PIVOT
(
AVG(StandardCost)
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
) AS PivotTable;

	

但使用過 PIVOT 的朋友都知道,當你有 N 個值要轉為樞紐資料行,您在 IN 就要寫 N 次,以上述範例只有 5 個樞紐資料行為例還算輕鬆,如果資料行幾十個那會是件很麻煩的事情,因此您可以透過 Dynamic SQL 的方式來幫您組織 PIVOT 所需的樞紐資料行。

【實作步驟】

以下實作步驟以 Northwind 資料庫為範例資料庫,實作之前請至 CodePlex 網站下載。

由於樞紐資料行必須在列舉要變成資料行標頭之值,因此我們可以利用下列指令碼來取得所有員工的 LastName。

DECLARE @cols NVARCHAR(MAX)= N'' --儲存動態欄位之用

	
--取得資料行列表
SELECT @cols = @cols + iif(@cols = N'',QUOTENAME(LastName),N',' + QUOTENAME(LastName))
FROM 
(
    SELECT DISTINCT(LastName) 
    FROM Employees 
) t

執行結果如下:

image

接著就可以把樞紐資料行的清單加到 SQL 字串變數中,最後使用 sp_executesql 來執行:

DECLARE @sql NVARCHAR(MAX)
SET @sql = N'
SELECT ' + @cols + '
FROM
(
  select c.LastName,sum(b.Quantity * b.UnitPrice) Amount
    from Orders a
    join [Order Details] b
    on a.OrderID = b.OrderID
    join Employees c
    on a.EmployeeID = c.EmployeeID
    group by c.EmployeeID,c.LastName
) AS j
PIVOT
(
  SUM(Amount) 
  FOR LastName
  IN ('
  + @cols
  + ')
) AS t'

	

	
EXEC sp_executesql @sql

執行結果如下:

image

完整程式碼如下:

DECLARE @cols NVARCHAR(MAX)= N'' --儲存動態欄位之用

	
--取得資料行列表
SELECT @cols = @cols + iif(@cols = N'',QUOTENAME(LastName),N',' + QUOTENAME(LastName))
FROM 
(
    SELECT DISTINCT(LastName) 
    FROM Employees 
) t

	
print @cols

	
--組動態SQL並使用sp_executesql來執行
DECLARE @sql NVARCHAR(MAX)
SET @sql = N'
SELECT ' + @cols + '
FROM
(
  select c.LastName,sum(b.Quantity * b.UnitPrice) Amount
    from Orders a
    join [Order Details] b
    on a.OrderID = b.OrderID
    join Employees c
    on a.EmployeeID = c.EmployeeID
    group by c.EmployeeID,c.LastName
) AS j
PIVOT
(
  SUM(Amount) 
  FOR LastName
  IN ('
  + @cols
  + ')
) AS t'

	
PRINT @sql
EXEC sp_executesql @sql

若您要使用只需將資料行列表的子查詢以及PIVOT所需的來源查詢,替換成符合您的情境之查詢指令碼即可輕鬆的使用 PIVOT 關係運算子所帶來的便利。

參考資料

- Cross-Tab Reports

- How to rotate a table in SQL Server

- 使用 PIVOT 和 UNPIVOT

- Script to create dynamic PIVOT queries in SQL Server