德瑞克：SQL Server 學習筆記: 動態 PIVOT 陳述式：Dynamic PIVOT

2013-02-22

動態 PIVOT 陳述式：Dynamic PIVOT

建立動態 PIVOT 陳述式：Create Dynamic PIVOT

適用版本：SQL Server 2005、2008、2008 R2、2012等版本。

SQL Server 2005 版本，提供 PIVOT 和 UNPIVOT 關係運算子，將資料表值運算式變更為另一個資料表。

PIVOT 會將運算式內一個資料行中的唯一值轉成輸出中的多個資料行，以旋轉資料表值運算式，然後依據最終輸出的需要，對其餘的任何資料行值執行必要的彙總。

UNPIVOT 執行的作業則與 PIVOT 相反，它會將資料表值運算式旋轉為資料行值。

PIVOT 提供的語法比您另外指定一連串複雜的 SELECT...CASE 陳述式，還要簡單易讀。

若要使用 PIVOT 和 UNPIVOT 關係運算子，資料庫的相容性層級必須設為 90 或更高。

準備工作：建立範例資料表

請參考以下的範例程式碼：

-- 建立資料表
USE Northwind_Dev
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[myInvoices]') AND type in (N'U'))
DROP TABLE [dbo].[myInvoices]
GO
SELECT CustomerID, DATEPART(M,OrderDate) N'OrderMonth', SUM(UnitPrice) N'SubTotal'
 INTO myInvoices
FROM Northwind.dbo.Invoices
GROUP BY DATEPART(M,OrderDate),CustomerID
GO

-- 查詢資料表：myInvoices
SELECT * FROM myInvoices
ORDER BY CustomerID,OrderMonth
GO

-- 01_檢視資料表_myInvoices

EX1. 使用 PIVOT，呈現各月(1~12月)的客戶消費彙總金額

-- EX1. 使用 PIVOT，呈現各月(1~12月)的客戶消費彙總金額

-- OrderMonth 資料行，資料類型是 INT

/*
在 PIVOT 的 IN 子句內：
使用數字當做資料行的名稱時，前後使用方括號[] 或 雙引號 ""
*/
SELECT *
FROM myInvoices
 PIVOT (SUM(SubTotal)
  FOR OrderMonth IN ([1],[2],[3], [4], [5],[6],[7],[8],[9],[10],[11],[12]) ) pvt
GO

-- 02_使用 PIVOT，呈現各月(1~12月)的客戶消費彙總金額

EX2. 動態組合 PIVOT 陳述式，呈現各月(1~12月)的客戶消費彙總金額

-- EX2. 動態組合 PIVOT 陳述式，呈現各月(1~12月)的客戶消費彙總金額

-- OrderMonth 資料行，資料類型是 INT

/*
QUOTENAME (Transact-SQL)
傳回 Unicode 字串，且附加了分隔符號，以便使輸入字串成為有效的 SQL Server 分隔識別碼。
*/

-- 01_sp_executesql + PIVOT + COALESCE() + QUOTENAME：適用 SQL Server 2005 版本

DECLARE @ColumnGroup NVARCHAR(MAX), @PivotSQL NVARCHAR(MAX) 

SELECT @ColumnGroup = COALESCE(@ColumnGroup + ',' ,'' ) + QUOTENAME(OrderMonth) 
FROM myInvoices 
GROUP BY QUOTENAME(OrderMonth) 

SELECT @PivotSQL = N'
SELECT * FROM myInvoices PIVOT (SUM(SubTotal) FOR OrderMonth 
 IN (' + @ColumnGroup +  N') ) AS pvt'

EXEC sp_executesql  @PivotSQL;
GO

-- 03_區域變數@ColumnGroup

-- 04_區域變數@PivotSQL

EX3. CategoryName 資料行，資料類型是字串

-- EX3. CategoryName 資料行，資料類型是字串

USE Northwind_Dev
GO
SELECT * FROM dbo.[Product Sales for 1997]
GO

-- 05_查詢檢視表_[Product Sales for 1997]

-- 01_使用 PIVOT

SELECT *
FROM dbo.[Product Sales for 1997]
 PIVOT (SUM(ProductSales)
  FOR CategoryName IN ([Beverages],[Condiments],[Confections], [Dairy Products], [Grains/Cereals],[Meat/Poultry],[Produce],[Seafood]) ) pvt
GO

-- 06_PIVOT_CategoryName 資料行，資料類型是字串

-- 02_動態組合 PIVOT 陳述式：sp_executesql + PIVOT + COALESCE() + QUOTENAME：適用 SQL Server 2005 版本

DECLARE @ColumnGroup NVARCHAR(MAX), @PivotSQL NVARCHAR(MAX) 

SELECT @ColumnGroup = COALESCE(@ColumnGroup + ',' ,'' ) + QUOTENAME(CategoryName) 
FROM dbo.[Product Sales for 1997]
GROUP BY QUOTENAME(CategoryName) 

SELECT @PivotSQL = N'
SELECT * FROM dbo.[Product Sales for 1997] PIVOT (SUM(ProductSales) FOR CategoryName 
 IN (' + @ColumnGroup +  N') ) AS pvt' 

EXEC sp_executesql  @PivotSQL;
GO

-- 07_區域變數@ColumnGroup