在SQL Server中，PIVOT是一個(gè)用于將行數(shù)據(jù)轉(zhuǎn)換為列數(shù)據(jù)的操作。它特別適用于將多個(gè)行中的值轉(zhuǎn)換為多個(gè)列的情況，并在此過(guò)程中執(zhí)行聚合操作。以下是關(guān)于SQL Server中PIVOT操作的詳細(xì)解釋和示例：

1、本文內(nèi)容

• 概述
• 語(yǔ)法
• 備注
• 關(guān)鍵點(diǎn)
• 簡(jiǎn)單 PIVOT 示例

適用于：

• SQL Server
• Azure SQL 數(shù)據(jù)庫(kù)
• Azure SQL 托管實(shí)例
• Azure Synapse Analytics
• Analytics Platform System (PDW)

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

PIVOT 的語(yǔ)法比一系列復(fù)雜的 SELECT…CASE 語(yǔ)句中所指定的語(yǔ)法更簡(jiǎn)單和更具可讀性。
有關(guān) PIVOT 語(yǔ)法的完整說(shuō)明，請(qǐng)參閱 FROM (Transact-SQL)。
https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver16
https://learn.microsoft.com/en-us/sql/t-sql/queries/from-transact-sql?view=sql-server-ver16

2、PIVOT概述

• 目的：將列值旋轉(zhuǎn)為列名（即行轉(zhuǎn)列），并在必要時(shí)對(duì)最終輸出中所需的任何其余列值執(zhí)行聚合。
• 使用場(chǎng)景：當(dāng)需要從多行數(shù)據(jù)中提取特定列的唯一值，并將這些值轉(zhuǎn)換為列標(biāo)題時(shí)。

3、語(yǔ)法

SELECT <non-pivoted column>,                                           -- [非透視的列],
    [first pivoted column] AS <column name>,                           -- [第一個(gè)透視的列] AS [列名稱1]
    [second pivoted column] AS <column name>,                          -- [第二個(gè)透視的列] AS [列名稱2]         
    ...  
    [last pivoted column] AS <column name>                             -- [最后一個(gè)透視的列] AS [列名稱N]
FROM  
    (<SELECT query that produces the data>)   
    AS <alias for the source query>  
PIVOT  
(  
    <aggregation function>(<column being aggregated>)                  -- [聚合函數(shù)]([要聚合的列]) 
FOR   
[<column that contains the values that will become column headers>]    -- [<包含要成為列標(biāo)題的值的列>]
    IN ( [first pivoted column], [second pivoted column],  
    ... [last pivoted column])                                         -- [第一個(gè)透視的列],   [第二個(gè)透視的列],  ...  [最后一個(gè)透視的列]
) AS <alias for the pivot table>  
<optional ORDER BY clause>;

4、備注

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

在 Microsoft Fabric 和 Azure Synapse Analytics 池中，如果 PIVOT 輸出的非 pivot 列上存在 GROUP BY，則 PIVOT 運(yùn)算符的查詢將失敗。 解決方法是從 GROUP BY 中刪除非 pivot 列。 查詢結(jié)果是相同的，因?yàn)榇?GROUP BY 子句是重復(fù)的。

5、關(guān)鍵點(diǎn)

• PIVOT必須列舉值：在PIVOT操作中，必須明確列舉出要轉(zhuǎn)換為列標(biāo)題的值。這些值將作為新表的字段名稱。
• 聚合函數(shù)：PIVOT操作中通常需要使用聚合函數(shù)（如SUM、AVG、MAX、MIN等）對(duì)數(shù)據(jù)進(jìn)行聚合。雖然語(yǔ)法中沒(méi)有明確顯示GROUP BY子句，但PIVOT實(shí)際上是隱式地對(duì)數(shù)據(jù)進(jìn)行分組和聚合的。
• 處理空值：如果在原始表中某個(gè)分組沒(méi)有對(duì)應(yīng)的數(shù)據(jù)，那么PIVOT后的新表中該分組對(duì)應(yīng)的列將以NULL值存在。
• 與UNPIVOT的關(guān)系：PIVOT和UNPIVOT是相反的操作。UNPIVOT將列轉(zhuǎn)換為列值，而PIVOT則將列值轉(zhuǎn)換為列。

6、簡(jiǎn)單 PIVOT 示例

示例表信息,顯示2024年每月的V-CUT和UV固化，背鉆流程工步的過(guò)賬面積

select * from t_PassOver_pivot
go

PassOver_Month OutTechNo TechName                       OutQty_Area
-------------- --------- ------------------------------ ----------------------
2024-02        1803      V-CUT                          454.96
2024-03        1803      V-CUT                          1054.38
2024-04        1803      V-CUT                          1139
2024-01        1803      V-CUT                          891.28
2024-05        1803      V-CUT                          1248.33
2024-02        1610      UV固化                          2881.89
2024-01        1610      UV固化                          4281.75
2024-04        1610      UV固化                          4832.2
2024-03        1610      UV固化                          5430.31
2024-05        1610      UV固化                          4840.63
2024-01        1715      背鉆                            1807.23
2024-05        1715      背鉆                            1406.53

但是 1715 背鉆 沒(méi)有 2024-02,2024-03,2024-04 3月的過(guò)賬面積。

以下代碼顯示相同的結(jié)果，該結(jié)果經(jīng)過(guò)透視以使 PassOver_Month 過(guò)賬月份值成為列標(biāo)題。

SELECT OutTechNo,TechName,
       [2024-01] AS Month_202401,[2024-02] AS Month_202402,[2024-03] AS Month_202403,[2024-04] AS Month_202404,[2024-05] AS Month_202405
 FROM t_PassOver_pivot /*數(shù)據(jù)源*/
AS P
PIVOT 
(
    SUM(OutQty_Area/*行轉(zhuǎn)列后 列的值*/) FOR 
    p.PassOver_Month/*需要行轉(zhuǎn)列的列*/ 
	IN ([2024-01],[2024-02],[2024-03],[2024-04],[2024-05]
	/*列的值*/)
) AS T

提供個(gè)五列表示2024年前五個(gè)月份，因1715 背鉆流程工步 沒(méi)有 2024-02,2024-03,2024-04 3月的過(guò)賬面積，即使結(jié)果為 NULL。

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

7、UNPIVOT

UNPIVOT 逆透視示例
與 PIVOT 執(zhí)行的操作幾乎相反，UNPIVOT將列轉(zhuǎn)換為列值即多列轉(zhuǎn)換為一列，而PIVOT則將列值轉(zhuǎn)換為列即一列的多行數(shù)據(jù)轉(zhuǎn)為多列。

select * from t_PassOver_unpivot

數(shù)據(jù)表t_PassOver_unpivot如下信息

SELECT PassOver_Month,OutTechNo,TechName,OutQty_Area  
FROM   
   (SELECT OutTechNo,TechName,Month_202401, Month_202402,Month_202403,Month_202404,Month_202405
    FROM t_PassOver_unpivot) p  
UNPIVOT  
   (OutQty_Area FOR PassOver_Month IN   
      (Month_202401, Month_202402,Month_202403,Month_202404,Month_202405)  
)AS unpvt;  
GO  

請(qǐng)注意，UNPIVOT 并不完全是 PIVOT 的逆操作。 PIVOT 執(zhí)行聚合，并將多個(gè)可能的行合并為輸出中的一行。 UNPIVOT 不重現(xiàn)原始表值表達(dá)式的結(jié)果，因?yàn)樾幸驯缓喜ⅰ?/p>

另外，UNPIVOT 輸入中的 NULL 值也在輸出中消失了。 如果值消失，表明在執(zhí)行 PIVOT 操作前，輸入中可能就已存在原始 NULL 值。

到此這篇關(guān)于SQLServer使用 PIVOT 和 UNPIVOT行列轉(zhuǎn)換的文章就介紹到這了,更多相關(guān)SQL PIVOT 和 UNPIVOT行列轉(zhuǎn)換內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論