Pivot
如您所知，關系表是表格化的，即，它們以列-值對的形式出現
CUST_ID STATE_CODE TIMES_PURCHASED
------- ---------- ---------------
1 CT 1
2 NY 10
3 NJ 2
4 NY 4
select * from (
select times_purchased, state_code
from customers t
)
pivot
(
count(state_code)
for state_code in ('NY','CT','NJ','FL','MO')
)
order by times_purchased
/
輸出如下：
'NY' 'CT' 'NJ' 'FL' 'MO'
---------- ---------- ----------
10 1 2 ....
Unpivot
就像有物質就有反物質一樣，有 pivot 就應該有“unpivot”，
原查詢:
SELECT [Years],[Months],[Type],[I],[II],[III],[VI],[Total]
FROM [ITShare].[dbo].[IT_HardWare_Month]
行轉列:
SELECT Years,Months,[Type],val,(case Sort when 'I' then '優' when 'II' then '良' when 'III' then '中' else '差' end) Sort,Sort as Sort1,Total
FROM [IT_HardWare_Month]
unpivot(val for Sort in(I,II,III,VI)) as b
MSDN
http://msdn.microsoft.com/zh-cn/library/ms177410.aspx
使用 PIVOT 和 UNPIVOT
SQL Server 2008 R2 其他版本
- SQL Server 2008
- SQL Server 2005
可以使用 PIVOT 和 UNPIVOT 關系運算符将表值表達式更改為另一個表。PIVOT 通過将表達式某一列中的唯一值轉換為輸出中的多個列來旋轉表值表達式，并在必要時對最終輸出中所需的任何其餘列值執行聚合。UNPIVOT 與 PIVOT 執行相反的操作，将表值表達式的列轉換為列值。
 |
|---|
| 對更新到 SQL Server 2005 或更高版本的資料庫使用 PIVOT 和 UNPIVOT 時，必須将資料庫的相容級别設定為 90 或更高。有關如何設定資料庫相容級别的資訊，請參閱 sp_dbcmptlevel (Transact-SQL)。 |
PIVOT 提供的文法比一系列複雜的 SELECT...CASE 語句中所指定的文法更簡單和更具可讀性。有關 PIVOT 文法的完整說明，請參閱 FROM (Transact-SQL)。
以下是帶批注的 PIVOT 文法。
SELECT <非透視的列>,
[第一個透視的列] AS <列名稱>,
[第二個透視的列] AS <列名稱>,
...
[最後一個透視的列] AS <列名稱>,
FROM
(<生成資料的 SELECT 查詢>)
AS <源查詢的别名>
PIVOT
(
<聚合函數>(<要聚合的列>)
FOR
[<包含要成為列标題的值的列>]
IN ( [第一個透視的列], [第二個透視的列],
... [最後一個透視的列])
) AS <透視表的别名>
<可選的 ORDER BY 子句>;
簡單 PIVOT 示例
下面的代碼示例生成一個兩列四行的表。
複制
USE AdventureWorks2008R2 ;
GO
SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost
FROM Production.Product
GROUP BY DaysToManufacture;
下面是結果集：
DaysToManufacture AverageCost
0 5.0885
1 223.88
2 359.1082
4 949.4105
沒有定義
DaysToManufacture
為 3 的産品。
以下代碼顯示相同的結果，該結果經過透視以使
DaysToManufacture
值成為列标題。提供一個清單示三
[3]
天，即使結果為
NULL
。
複制
-- Pivot table with one row and five columns
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;
下面是結果集：
Cost_Sorted_By_Production_Days 0 1 2 3 4
AverageCost 5.0885 223.88 359.1082 NULL 949.4105
複雜 PIVOT 示例
可能會用到
PIVOT
的常見情況是：需要生成交叉表格報表以彙總資料。例如，假設需要在
AdventureWorks2008R2
示例資料庫中查詢
PurchaseOrderHeader
表以确定由某些特定雇員所下的采購訂單數。以下查詢提供了此報表（按供應商排序）。
複制
USE AdventureWorks2008R2;
GO
SELECT VendorID, [250] AS Emp1, [251] AS Emp2, [256] AS Emp3, [257] AS Emp4, [260] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [250], [251], [256], [257], [260] )
) AS pvt
ORDER BY pvt.VendorID;
以下為部分結果集。
VendorID Emp1 Emp2 Emp3 Emp4 Emp5
1492 2 5 4 4 4
1494 2 5 4 5 4
1496 2 4 4 5 5
1498 2 5 4 4 4
1500 3 4 4 5 4
将在
EmployeeID
列上透視此嵌套 select 語句傳回的結果。
複制
SELECT PurchaseOrderID, EmployeeID, VendorID
FROM PurchaseOrderHeader;
這意味着
EmployeeID
列傳回的唯一值自行變成了最終結果集中的字段。是以，在透視子句中指定的每個
EmployeeID
号都有相應的一列：在本例中為雇員
164
、
198
、
223
、
231
和
233
。
PurchaseOrderID
列作為值列，将根據此列對最終輸出中傳回的列（稱為分組列）進行分組。在本例中，通過
COUNT
函數聚合分組列。請注意，将顯示一條警告消息，指出為每個雇員計算
COUNT
時未考慮顯示在
PurchaseOrderID
列中的任何空值。
| |
|---|
| 如果聚合函數與 PIVOT 一起使用，則計算聚合時将不考慮出現在值列中的任何空值。 |
UNPIVOT 将與 PIVOT 執行幾乎完全相反的操作，将列轉換為行。假設以上示例中生成的表在資料庫中存儲為
pvt
，并且您需要将列辨別符
Emp1
、
Emp2
、
Emp3
、
Emp4
和
Emp5
旋轉為對應于特定供應商的行值。這意味着必須辨別另外兩個列。包含要旋轉的列值（
Emp1
、
Emp2
...）的列将被稱為
Employee
，将儲存目前位于待旋轉列下的值的列被稱為
Orders
。這些列分别對應于 Transact-SQL 定義中的 pivot_column 和 value_column。以下為該查詢。
複制
--Create the table and insert values as portrayed in the previous example.
CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
Emp3 int, Emp4 int, Emp5 int);
GO
INSERT INTO pvt VALUES (1,4,3,5,4,4);
INSERT INTO pvt VALUES (2,4,1,5,5,5);
INSERT INTO pvt VALUES (3,4,3,5,4,4);
INSERT INTO pvt VALUES (4,4,2,5,5,4);
INSERT INTO pvt VALUES (5,5,1,5,5,5);
GO
--Unpivot the table.
SELECT VendorID, Employee, Orders
FROM
(SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
FROM pvt) p
UNPIVOT
(Orders FOR Employee IN
(Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt;
GO
以下為部分結果集。
VendorID Employee Orders
---------- ---------- ------
1 Emp1 4
1 Emp2 3
1 Emp3 5
1 Emp4 4
1 Emp5 4
2 Emp1 4
2 Emp2 1
2 Emp3 5
2 Emp4 5
2 Emp5 5
...
請注意，UNPIVOT 并不完全是 PIVOT 的逆操作。PIVOT 會執行一次聚合，進而将多個可能的行合并為輸出中的單個行。而 UNPIVOT 不會重制原始表值表達式的結果，因為行已經被合并了。另外，UNPIVOT 的輸入中的空值不會顯示在輸出中，而在執行 PIVOT 操作之前，輸入中可能有原始的空值。
AdventureWorks2008R2 示例資料庫中的 Sales.vSalesPersonSalesByFiscalYears 視圖将使用 PIVOT 傳回每個銷售人員在每個會計年度的總銷售額。若要在 SQL Server Management Studio 中編寫視圖腳本，請在“對象資料總管”中，在“視圖”檔案夾下找到 AdventureWorks2008R2 資料庫對應的視圖。右鍵單擊該視圖名稱，再選擇“編寫視圖腳本為”。
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)