積沙成塔

【情境說明】

在 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

執行結果如下：

接著就可以把樞紐資料行的清單加到 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

執行結果如下：

完整程式碼如下：

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