撰寫樞紐和取消樞紐結果集的查詢
使用 SQL Server 中的樞紐將資料顯示方向從資料列方向扭轉為資料行方向。 樞紐處理時,您要將資料行中的值合併到相異值清單,然後將此清單投影為資料行標題。 一般而言,這包括在新資料行中彙總資料行值。
例如,下列部分來源資料會列出 [Category] 和 [Orderyear] 的重複值,以及每對 [Category/Orderyear] 執行個體的 [Qty] 值:
類別 | 數量 | Orderyear |
---|---|---|
乳製品 | 12 | 2019 |
穀物/麥片 | 10 | 2019 |
乳製品 | 5 | 2019 |
海鮮 | 2 | 2020 |
Confections | 36 | 2020 |
Condiments | 35 | 2020 |
Confections | 55 | 2020 |
Condiments | 16 | 2020 |
乳製品 | 60 | 2020 |
乳製品 | 20 | 2020 |
Confections | 24 | 2020 |
...(2155 筆資料列受影響) |
上表展示 2000 多筆資料列,其中包含許多重複值。 若要依類別和年度分析結果,您可能想要依下列方式排列要顯示的值,並一路加總 [Qty] 資料行:
類別 | 2019 | 2020 | 2021 |
---|---|---|---|
Beverages | 1842 | 3996 | 3694 |
Condiments | 962 | 2895 | 1441 |
Confections | 1357 | 4137 | 2412 |
乳製品 | 2086 | 4374 | 2689 |
穀物/麥片 | 549 | 2636 | 1377 |
肉/家禽 | 950 | 2189 | 1060 |
製造 | 549 | 1583 | 858 |
海鮮 | 1286 | 3679 | 2716 |
(8 筆資料列受影響) |
結果集現為八筆資料列總計。 在樞紐程序中,每個相異年度都建立為資料行標題,而 [Qty] 資料行中的值會依 [Category] 分組並彙總。
使用 PIVOT 樞紐結果集
您可以使用 PIVOT 運算子來樞紐結果集。 Transact-SQL PIVOT 資料表運算子適用於 SELECT 陳述式中的 FROM 子句輸出。 若要使用 PIVOT,您必須為運算子提供三個元素:
- 分組:在 FROM 子句中提供輸入資料行。 在這些資料行中,PIVOT 會決定要使用哪一個資料行來分組資料以供彙總。 這取決於哪些資料行未被當作 PIVOT 運算子中的其他元素。
- 散佈:您提供一份逗號分隔的值清單,當作樞紐資料的資料行標題。 這些值必須出現在來源資料中。
- 彙總:您提供對分組資料列執行的彙總函式 (SUM 等等)。
此外,您必須為 PIVOT 運算子的結果資料表指派資料表別名。 下列範例顯示就位的元素:
SELECT Category, [2019],[2020],[2021]
FROM ( SELECT Category, Qty, Orderyear FROM Sales.CategoryQtyYear) AS D
PIVOT(SUM(qty) FOR orderyear IN ([2019],[2020],[2021])) AS pvt;
在上例中,[Orderyear] 是提供散佈值的資料行、[Qty] 用於彙總,而 [Category] 則用於分組。 [Orderyear] 的值會以分隔符號括住,表示其為結果資料行的識別碼。
使用 UNPIVOT 取消樞紐結果集
取消樞紐資料是樞紐資料的邏輯反向。 取消樞紐會將資料行轉換成資料列,而不是將資料列轉換成資料行。 這個實用的技巧非常適合經過樞紐處理的資料 (無論使用 Transact-SQL PIVOT 運算子與否),將其回復成資料列方向的表格式顯示。 您可以使用 UNPIVOT 資料表運算子來完成此作業。
若要使用 UNPIVOT 運算子,您要提供三個元素:
- 要取消樞紐的來源資料行。
- 顯示取消樞紐值的新資料行名稱。
- 顯示取消樞紐值名稱的資料行名稱。
下列範例會使用新的資料行名稱 [orderyear] 以及要顯示在新 [qty] 資料行中的 [qty] 值,指定 2019、2020 和 2021 為要取消樞紐的資料行。
SELECT category, qty, orderyear
FROM Sales.PivotedCategorySales
UNPIVOT(qty FOR orderyear IN([2019],[2020],[2021])) AS unpvt;
取消樞紐資料時,會將一或多筆資料行定義為要轉換成資料列的來源。 這些資料行中的資料會散佈或分割成一或多筆新資料列,視要取消樞紐的資料行數目而定。 在下列來源資料中,有三筆資料行會被取消樞紐。 每個 [Orderyear] 的值都會複製到新的資料列,與其 [Category] 值建立關聯。 在此程序中會移除所有 Null,而且不建立任何資料列:
類別 | 2019 | 2020 | 2021 |
---|---|---|---|
Beverages | 1842 | 3996 | 3694 |
Condiments | 962 | 2895 | 1441 |
Confections | 1357 | 4137 | 2412 |
乳製品 | 2086 | 374 | 2689 |
穀物/麥片 | 549 | 2636 | 1377 |
肉/家禽 | 950 | 2189 | 1060 |
製造 | 549 | 1583 | 858 |
海鮮 | 1286 | 3679 | 2716 |
[Category] 和 [Orderyear] 每次交集都會建立新的資料列,如下列部分結果所示:
category | qty | orderyear |
---|---|---|
Beverages | 1842 | 2019 |
Beverages | 3996 | 2020 |
Beverages | 3694 | 2021 |
Condiments | 962 | 2019 |
Condiments | 2895 | 2020 |
Condiments | 1441 | 2021 |
Confections | 1357 | 2019 |
Confections | 4137 | 2020 |
Confections | 2412 | 2021 |
取消樞紐不會還原原始資料。 在原始樞紐的彙總程序期間,遺失了詳細資料層級的資料。 UNPIVOT 無法配置值,所以無法回復到原始的詳細資料值。