91&

SQL Server 使用 PIVOT 和 UNPIVOT 實(shí)現(xiàn)多條行記錄轉(zhuǎn)置為列表

當(dāng)前位置：點(diǎn)晴教程→知識管理交流 →『技術(shù)文檔交流』

admin

2023年6月28日 9:16 本文熱度 1206

可以使用 PIVOT 和 UNPIVOT 關(guān)系運(yùn)算符將表值表達(dá)式更改為另一個(gè)表。 PIVOT 通過將表達(dá)式中的一個(gè)列的唯一值轉(zhuǎn)換為輸出中的多列，來輪替表值表達(dá)式。 PIVOT 在需要對最終輸出所需的所有剩余列值執(zhí)行聚合時(shí)運(yùn)行聚合。與 PIVOT 執(zhí)行的操作相反，UNPIVOT 將表值表達(dá)式的列輪換為列值。

注意

對升級到 SQL Server 2005 或更高版本的數(shù)據(jù)庫使用 PIVOT 和 UNPIVOT 時(shí)，必須將數(shù)據(jù)庫的兼容級別設(shè)置為 90 或更高。有關(guān)如何設(shè)置數(shù)據(jù)庫兼容級別的信息，請參閱 sp_dbcmptlevel (Transact-SQL)。

PIVOT 提供的語法比一系列復(fù)雜的 select...CASE 語句中所指定的語法更簡單和更具可讀性。有關(guān) PIVOT 語法的完整說明，請參閱 from (Transact-SQL)。

語法

以下語法總結(jié)了如何使用 PIVOT 運(yùn)算符：

select <non-pivoted column>,  
    [first pivoted column] AS <column name>,  
    [second pivoted column] AS <column name>,  
    ...  
    [last pivoted column] AS <column name>  
from  
    (<select query that produces the data>)   
    AS <alias for the source query>  
PIVOT  
(  
    <aggregation function>(<column being aggregated>)  
FOR   
[<column that contains the values that will become column headers>]   
    IN ( [first pivoted column], [second pivoted column],  
    ... [last pivoted column])  
) AS <alias for the pivot table>  
<optional ORDER BY clause>;   

以下是帶批注的 PIVOT 語法：

select <非透視的列>,
    [第一個(gè)透視的列] AS <列名稱>,
    [第二個(gè)透視的列] AS <列名稱>,
    ...
    [最后一個(gè)透視的列] AS <列名稱>,
from
    (<生成數(shù)據(jù)的 select 查詢>)
    AS <源查詢的別名>
PIVOT
(
    <聚合函數(shù)>(<要聚合的列>)
FOR
[<包含要成為列標(biāo)題的值的列>]
    IN ( [第一個(gè)透視的列], [第二個(gè)透視的列],
    ... [最后一個(gè)透視的列])
) AS <透視表的別名>
<可選的 ORDER BY 子句>;

備注

UNPIVOT 子句中的列標(biāo)識符需遵循目錄排序規(guī)則。對于 SQL 數(shù)據(jù)庫，排序規(guī)則始終是 SQL_Latin1_General_CP1_CI_AS。對于 SQL Server 部分包含的數(shù)據(jù)庫，排序規(guī)則始終是 Latin1_General_100_CI_AS_KS_WS_SC。如果將該列與與其他列合并，則需要 collate 子句 (COLLATE DATABASE_DEFAULT) 以避免沖突。

簡單 PIVOT 示例

下面的代碼示例生成一個(gè)兩列四行的表：

USE AdventureWorks2014 ;  
GO  
select DaysToManufacture, AVG(StandardCost) AS AverageCost   
from Production.Product  
GROUP BY DaysToManufacture;  

下面是結(jié)果集：

DaysToManufacture AverageCost
----------------- -----------
0                 5.0885
1                 223.88
2                 359.1082
4                 949.4105

沒有定義 DaysToManufacture 為 3 的產(chǎn)品。

以下代碼顯示相同的結(jié)果，該結(jié)果經(jīng)過透視以使 DaysToManufacture 值成為列標(biāo)題。提供一個(gè)列表示三 [3] 天，即使結(jié)果為 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;  

下面是結(jié)果集：

Cost_Sorted_By_Production_Days 0           1           2           3           4         
------------------------------ ----------- ----------- ----------- ----------- -----------
AverageCost                    5.0885      223.88      359.1082    NULL        949.4105

復(fù)雜 PIVOT 示例

若要生成交叉表報(bào)表來匯總數(shù)據(jù)，通常可能會發(fā)現(xiàn) PIVOT 很有用。例如，假設(shè)需要在 PurchaseOrderHeader 示例數(shù)據(jù)庫中查詢 AdventureWorks2014 表以確定由某些特定雇員所下的采購訂單數(shù)。以下查詢提供了此報(bào)表（按供應(yīng)商排序）：

USE AdventureWorks2014;  
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;  

以下為部分結(jié)果集：

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 語句返回的結(jié)果：

select PurchaseOrderID, EmployeeID, VendorID  
from PurchaseOrderHeader; 

EmployeeID 列返回的唯一值變成了最終結(jié)果集中的字段。因此，在 pivot 子句中指定的每個(gè) EmployeeID 號都有對應(yīng)的列：在此示例中，為員工 250、251、256、257 和 260。PurchaseOrderID 列作為值列，將根據(jù)此列對最終輸出中返回的列（稱為分組列）進(jìn)行分組。在本例中，通過 COUNT 函數(shù)聚合分組列。請注意，系統(tǒng)會顯示警告消息，以指明在為每個(gè)員工計(jì)算 COUNT 時(shí)，未考慮 PurchaseOrderID 列中的任何 NULL 值。

重要提示

如果聚合函數(shù)與 PIVOT 一起使用，則計(jì)算聚合時(shí)將不考慮出現(xiàn)在值列中的任何空值。

逆透視示例

與 PIVOT 執(zhí)行的操作幾乎相反，UNPIVOT 將列輪換為行。假設(shè)以上示例中生成的表在數(shù)據(jù)庫中存儲為 pvt，并且您需要將列標(biāo)識符 Emp1、Emp2、Emp3、Emp4 和 Emp5 旋轉(zhuǎn)為對應(yīng)于特定供應(yīng)商的行值。因此，必須標(biāo)識另外兩個(gè)列。包含要輪換的列值（Emp1、Emp2...）的列稱為 Employee，保留要輪換列下的現(xiàn)有值的列稱為 Orders。這些列分別對應(yīng)于 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  

以下為部分結(jié)果集：

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 執(zhí)行聚合，并將多個(gè)可能的行合并為輸出中的一行。UNPIVOT 不重現(xiàn)原始表值表達(dá)式的結(jié)果，因?yàn)樾幸驯缓喜ⅰＡ硗猓?code style="box-sizing: inherit; outline-color: inherit; font-family: SFMono-Regular, Consolas, "Liberation Mono", Menlo, Courier, monospace; font-size: 13.6px; direction: ltr; background-color: var(--theme-inline-code); overflow-wrap: break-word; border-radius: 3px; padding: 0.1em 0.2em;">UNPIVOT 輸入中的 NULL 值也在輸出中消失了。如果值消失，表明在執(zhí)行 PIVOT 操作前，輸入中可能就已存在原始 NULL 值。

AdventureWorks2022 示例數(shù)據(jù)庫中的 Sales.vSalesPersonSalesByFiscalYears 視圖將使用 PIVOT 返回每個(gè)銷售人員在每個(gè)會計(jì)年度的總銷售額。若要在 SQL Server Management Studio 中編寫視圖腳本，請?jiān)凇皩ο筚Y源管理器”中的“視圖”文件夾下找到 AdventureWorks2022 數(shù)據(jù)庫對應(yīng)的視圖。右鍵單擊該視圖名稱，再選擇“編寫視圖腳本為” 。

另請參閱

from (Transact-SQL)
CASE (Transact-SQL)

該文章在 2023/6/28 9:19:10 編輯過

關(guān)鍵字查詢

相關(guān)文章

正在查詢...

點(diǎn)晴ERP是一款針對中小制造業(yè)的專業(yè)生產(chǎn)管理軟件系統(tǒng),系統(tǒng)成熟度和易用性得到了國內(nèi)大量中小企業(yè)的青睞。

點(diǎn)晴PMS碼頭管理系統(tǒng)主要針對港口碼頭集裝箱與散貨日常運(yùn)作、調(diào)度、堆場、車隊(duì)、財(cái)務(wù)費(fèi)用、相關(guān)報(bào)表等業(yè)務(wù)管理，結(jié)合碼頭的業(yè)務(wù)特點(diǎn)，圍繞調(diào)度、堆場作業(yè)而開發(fā)的。集技術(shù)的先進(jìn)性、管理的有效性于一體，是物流碼頭及其他港口類企業(yè)的高效ERP管理信息系統(tǒng)。

點(diǎn)晴WMS倉儲管理系統(tǒng)提供了貨物產(chǎn)品管理,銷售管理,采購管理,倉儲管理,倉庫管理,保質(zhì)期管理,貨位管理,庫位管理,生產(chǎn)管理,WMS管理系統(tǒng),標(biāo)簽打印,條形碼,二維碼管理,批號管理軟件。

點(diǎn)晴免費(fèi)OA是一款軟件和通用服務(wù)都免費(fèi)，不限功能、不限時(shí)間、不限用戶的免費(fèi)OA協(xié)同辦公管理系統(tǒng)。

91网首页-91网页版-91网在线观看-91网站免费观看-91网站永久视频-91网站在线播放

SQL Server 使用 PIVOT 和 UNPIVOT 實(shí)現(xiàn)多條行記錄轉(zhuǎn)置為列表

語法

備注

簡單 PIVOT 示例

復(fù)雜 PIVOT 示例

逆透視示例

另請參閱