搜尋本站文章

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



-- 08_區域變數@PivotSQL







參考資料

使用 PIVOT 和 UNPIVOT
http://technet.microsoft.com/zh-tw/library/ms177410(v=sql.105).aspx

FROM (Transact-SQL)
http://technet.microsoft.com/zh-tw/library/ms177634(v=sql.110).aspx

SQL Server:認識 COALESCE() 函數
http://sharedderrick.blogspot.tw/2012/06/t-sql-coalesce.html