發表者討論內容

冷日
(冷日) 發表時間：2018/8/16 15:03

Webmaster

註冊日: 2008/2/19
來自:
發表數: 15771

[轉貼]使用 PIVOT 和 UNPIVOT

FROM - 使用 PIVOT 和 UNPIVOT
2017/03/16

本主題適用於：是SQL Server (從 2008 開始)是Azure SQL Database是Azure SQL 資料倉儲是平行處理資料倉儲

您可以使用 PIVOT 和 UNPIVOT 關係運算子，將資料表值運算式變更為另一個資料表。 PIVOT 會藉由將來自運算式中某個資料行的唯一值轉換成輸出中的多個資料行，來旋轉資料表值運算式，然後對最終輸出所需的任何其餘資料行值，視需要執行彙總。 UNPIVOT 執行的作業與 PIVOT 相反，它會將資料表值運算式的資料行旋轉成資料行值。

PIVOT 提供的語法比您另外指定一連串複雜的 SELECT...CASE 陳述式，還要簡單易讀。如需 PIVOT 語法的完整描述，請參閱 FROM (Transact-SQL)。
語法

下列語法摘要說明如何使用 PIVOT 運算子。

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

Remarks

UNPIVOT 子句中的資料行識別碼會依照目錄定序。就 SQL Database 而言，定序一律為 SQL_Latin1_General_CP1_CI_AS。就 [SQL Server] 部分自主資料庫而言，定序一律為 Latin1_General_100_CI_AS_KS_WS_SC。如果資料行與其他資料行結合，就必須使用定序子句 (COLLATE DATABASE_DEFAULT) 來避免衝突。
基本 PIVOT 範例

下列程式碼範例會產生包含兩個資料行的資料表，其中有四個資料列。

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

下列程式碼會顯示同樣的結果，但是經過樞紐處理後，讓 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 的用處。例如，假設您想要查詢 PurchaseOrderHeader 範例資料庫中的 AdventureWorks2014 資料表，以判斷某些員工所下的訂單數目。下列查詢會提供這個報表，並依供應商排序：

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;

以下為部分結果集。

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 PurchaseOrderID, EmployeeID, VendorID
FROM PurchaseOrderHeader;

這表示由 EmployeeID 資料行所傳回的唯一值本身會變成最終結果集中的欄位。因此，樞紐子句指定的每個 EmployeeID 編號都會有一個資料行：在此情況下，是以下員工 164、198、223、231 和 233。 PurchaseOrderID 資料行會當作數值資料行，這是在最終輸出中傳回的資料行 (稱為群組資料行) 所根據以進行分組的資料行。在此情況下，COUNT 函數會對群組資料行進行彙總。請注意，此時會顯示警告訊息，指出計算每個員工的 PurchaseOrderID 時，並未考慮 COUNT 資料行中出現的任何 NULL 值。

重要

搭配 PIVOT 使用彙總函數時，在計算彙總期間不會考慮值資料行中出現的任何 Null 值。

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 輸入中的 Null 值在輸出中會消失不見，但是在執行 PIVOT 作業之前，輸入中原先可能有原始 Null 值。

AdventureWorks2012 範例資料庫中的 Sales.vSalesPersonSalesByFiscalYears 檢視表會使用 PIVOT 來傳回每位銷售人員在每個會計年度的總銷售額。若要在 Transact-SQL 中編寫該檢視表的指令碼，請在 [物件總管] 中，於 AdventureWorks2012 資料庫的 [檢視表] 資料夾底下找出該檢視表。在檢視表名稱上按一下滑鼠右鍵，然後選取 [編寫檢視表的指令碼為]。

原文出處：使用 PIVOT 和 UNPIVOT | Microsoft Docs

回覆

冷日
(冷日) 發表時間：2018/8/16 15:07

Webmaster

註冊日: 2008/2/19
來自:
發表數: 15771

[轉貼]使用 PIVOT 「扭轉」查詢的結果

[SQL指令]使用 PIVOT 「扭轉」查詢的結果。把SQL查詢成果（直式），轉成Excel常用的（橫式）展示格式

想把橫向列資料改成多欄資料時，該怎麼作？
資料合併與轉向 / PIVOT 和 UNPIVOT。

假設我有一個 Table，記錄了每一個月、各分店的銷售金額（或是銷售量）

這樣的資料庫 Table如果要作年度的匯總運算，

很可能查出來的結果會是這個樣子。

年度	銷售量
2010	100,000
2009	80,000
2008	50,000

畢竟，Table裡面的每一列記錄，都是橫的（一列、一列）

透過 Group by或是 Count()計算之後，成果也必然是這樣。

但，有時候要產生這樣的報表，就很傷腦筋了。

偏偏大部分的 User滿喜歡這種輸出成果。

2008	2009	2010
50,000	80,000	100,000

如果您聽不懂我的敘述，請看以下的「第一篇推薦文章（張小呆的大作）」，有圖片解說。

新版本的 MS SQL 2005開始，多了新方法來處理這樣「資料表的結果 "逆轉"」

簡單的說，把原本（上圖一）的年度「欄位(Column)」，扭轉（轉向）成一列「資料列 (Row / 一筆紀錄)」

原廠的說明文件如下： http://msdn.microsoft.com/zh-tw/library/ms177410.aspx

使用 PIVOT 和 UNPIVOT

您可以使用 PIVOT 和 UNPIVOT 關係運算子，將資料表值運算式變更為另一個資料表。PIVOT 會將運算式內一個資料行中的唯一值轉成輸出中的多個資料行，以旋轉資料表值運算式，然後依據最終輸出的需要，對其餘的任何資料行值執行必要的彙總。UNPIVOT 執行的作業則與 PIVOT 相反，它會將資料表值運算式旋轉為資料行值。

Sorry......上面的說明的確很像火星文

但有三篇文章講得更淺顯易懂（我推薦這三篇文章）：

張小呆 http://www.dotblogs.com.tw/dc690216/archive/2010/02/04/13478.aspx...... 圖文並茂，很讚喔！

Rely1020 http://rely1020.blog.ithome.com.tw/post/1606/39111......把語法與理論講得很清楚。

看完上面兩篇文章的解釋，您多看一個範例（練習、比對一下），就會懂了。

黑暗執行緒
http://blog.darkthread.net/blogs/darkthreadtw/archive/2007/07/20/tips-using-pivot-in-sql-2005.aspx

---------------------------------------------------------------------------------------------------------------------------

2011/7/7 補充：

假設原本的 Table是這個樣子：

我們透過 PIVOT來扭轉輸出的成果：

上圖的這種格式，比較適合 Excel常用的展示格式，或是透過 excel繼續運算或是繪圖。

---------------------------------------------------------------------------------------------------------------------------

如果您不想使用（或是無法使用這種方法的話）

這本書的附錄 A也有另外一種解法。請看文章標題 -- 「想把 橫向列資料改成多欄資料時，該怎麼作？」頁數533

但我覺得這方法不如PIVOT 和 UNPIVOT好用

書名：SQL語法範例辭典（旗標出版社）

作者：朝井淳

另外，楊志強老師在旗標出版社推出的「T-SQL 實戰學堂」一書，

裡面的 Ch .6-4節（資料合併與轉向）也有解說 PIVOT 和 UNPIVOT。

如果不用 PIVOT的話，這裡也有指導您其他的寫法。（不是我寫的書，不方便直接COPY指令給大家看 Sorry!）

2011/10/5補充：

原文出處：[SQL指令]使用 PIVOT 「扭轉」查詢的結果。把SQL查詢成果（直式），轉成Excel常用的（橫式）展示格式 | ASP.NET專題實務教學影片入門實戰課程 -- MIS2000Lab. - 點部落

回覆

冷日
(冷日) 發表時間：2018/8/16 15:10

Webmaster

註冊日: 2008/2/19
來自:
發表數: 15771

[轉貼]SQL - 使用 PIVOT: SQL - 使用 PIVOT
摘要:SQL - 使用 PIVOT
從以前到現在再處理年度或是季的銷售資料總是讓人覺得麻煩，在不知道 PIVOT 時，應該會下很多的 T-SQL 來處理。自從 SQL Server 2005 開始就提供 PIVOT 這個很好用的語法，以下就來實作一下唄...

資料：

銷售業績

銷售業績By季

Code：

Select * From dbo.銷售業績 Order By 年份, 月份

SELECT
年份,
[01] as '一月', [02] as '二月', [03] as '三月', [04] as '四月',
[05] as '五月', [06] as '六月', [07] as '七月', [08] as '八月',
[09] as '九月', [10] as '十月', [11] as '十一月', [12] as '十二月'
FROM (
SELECT 年份, 月份, 銷售量
FROM dbo.銷售業績
GROUP BY 年份, 月份, 銷售量
) as GroupTable
PIVOT
(
Sum(銷售量)
FOR 月份 IN ([01], [02], [03], [04], [05], [06], [07], [08], [09], [10], [11], [12])
) AS PivotTable

結果：

Code：

Select * From dbo.[銷售業績By季] Order By 年份, 季, 月份
SELECT
年份,
[Q1] as '第一季', [Q2] as '第二季', [Q3] as '第三季', [Q4] as '第四季'
FROM (
SELECT 年份, 季, SUM(銷售量) as '季加總'
FROM dbo.[銷售業績By季]
GROUP BY 年份, 季
) as GroupTable
PIVOT
(
Sum(季加總)
FOR 季 IN ([Q1], [Q2], [Q3], [Q4])
) AS PivotTable

結果：

參考：
使用 PIVOT 和 UNPIVOT
原文出處：SQL - 使用 PIVOT | 張小呆的碎碎唸 - 點部落

回覆

冷日
(冷日) 發表時間：2018/8/16 15:12

Webmaster

註冊日: 2008/2/19
來自:
發表數: 15771

[轉貼]Using PIVOT In SQL 2005

TIPS-Using PIVOT In SQL 2005

遇到一個可以應用SQL 2005 PIVOT新功能的好例子，拿出來分享一下。

分析IIS Log時，我希望統計每一秒鐘不同執行結果(Status Code=200, 304, 401, 404, 500 ...)的次數，由IISLog匯入而成的Table可以找到LogTime與StatusCode兩個欄位。
要統計每秒不同StatusCode的數量不難，例如以下的T-SQL:

As Cnt

FROM IISLogTable

BY LogTime, StatusCode

BY LogTime

查出的結果會像這樣:

LogTime  StatusCode Cnt
-------- ---------- -----------
06:40:05 200        5
06:40:05 302        1
06:40:06 304        1
06:40:06 200        10
06:40:07 200        5
06:40:07 500        3
06:40:08 200        11
06:40:08 404        1

但在實際應用時，我們會希望將每秒鐘的各StatusCode整合在一列中，像LogTime, StatusCode, Count200, Count302, Count304, Count401...的形式，以方便用Excel製表或繪圖，但這少不了要費一番手腳。

當使用的資料庫是SQL 2005時，我們多了一項新武器---PIVOT! 用如下的語法，就可以將以上的結果由列轉成欄，變成我們要的樣子。

FROM

AS Cnt

    FROM IISLogTable

BY LogTime, StatusCode

) AS X

PIVOT

SUM(Cnt)

IN

    ([200],[302],[304],[401],[404],[500])

) AS PVT

查詢結果變成:

LogTime  200         302         304         401         404         500
-------- ----------- ----------- ----------- ----------- ----------- -----------
06:40:05 5           1           NULL        NULL        NULL        NULL
06:40:06 10          NULL        1           NULL        NULL        NULL
06:40:07 5           NULL        NULL        NULL        NULL        3
06:40:08 11          NULL        NULL        NULL        1           NULL

怎樣，很省事很酷吧?

原文出處： TIPS-Using PIVOT In SQL 2005 - 黑暗執行緒

回覆

主題一覽的下方

討論串

前一個主題 | 下一個主題 | 頁首 |

| |

發表新主題

DB研討會 : [轉貼]使用 PIVOT 和 UNPIVOT

[SQL指令]使用 PIVOT 「扭轉」查詢的結果。把SQL查詢成果（直式），轉成Excel常用的（橫式）展示格式

SQL - 使用 PIVOT

TIPS-Using PIVOT In SQL 2005