SQLServer使用 PIVOT 和 UNPIVOT行列转换_MsSqlserver

在sql server中，pivot是一个用于将行数据转换为列数据的操作。它特别适用于将多个行中的值转换为多个列的情况，并在此过程中执行聚合操作。以下是关于sql server中pivot操作的详细解释和示例：

1、本文内容

概述
语法
备注
关键点
简单 pivot 示例

适用于：

sql server
azure sql 数据库
azure sql 托管实例
azure synapse analytics
analytics platform system (pdw)

可以使用 pivot 和 unpivot 关系运算符将表值表达式更改为另一个表。 pivot 通过将表达式中的一个列的唯一值转换为输出中的多列，来轮替表值表达式。 pivot 在需要对最终输出所需的所有剩余列值执行聚合时运行聚合。与 pivot 执行的操作相反，unpivot 将表值表达式的列轮换为列值。

pivot 的语法比一系列复杂的 select…case 语句中所指定的语法更简单和更具可读性。
有关 pivot 语法的完整说明，请参阅 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概述

目的：将列值旋转为列名（即行转列），并在必要时对最终输出中所需的任何其余列值执行聚合。
使用场景：当需要从多行数据中提取特定列的唯一值，并将这些值转换为列标题时。

3、语法

select <non-pivoted column>,                                           -- [非透视的列],
    [first pivoted column] as <column name>,                           -- [第一个透视的列] as [列名称1]
    [second pivoted column] as <column name>,                          -- [第二个透视的列] as [列名称2]         
    ...  
    [last pivoted column] as <column name>                             -- [最后一个透视的列] as [列名称n]
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>;

4、备注

unpivot 子句中的列标识符需遵循目录排序规则。对于 sql 数据库，排序规则始终是 sql_latin1_general_cp1_ci_as。对于 sql server 部分包含的数据库，排序规则始终是 latin1_general_100_ci_as_ks_ws_sc。如果将该列与与其他列合并，则需要 collate 子句 (collate database_default) 以避免冲突。

在 microsoft fabric 和 azure synapse analytics 池中，如果 pivot 输出的非 pivot 列上存在 group by，则 pivot 运算符的查询将失败。解决方法是从 group by 中删除非 pivot 列。查询结果是相同的，因为此 group by 子句是重复的。

5、关键点

pivot必须列举值：在pivot操作中，必须明确列举出要转换为列标题的值。这些值将作为新表的字段名称。
聚合函数：pivot操作中通常需要使用聚合函数（如sum、avg、max、min等）对数据进行聚合。虽然语法中没有明确显示group by子句，但pivot实际上是隐式地对数据进行分组和聚合的。
处理空值：如果在原始表中某个分组没有对应的数据，那么pivot后的新表中该分组对应的列将以null值存在。
与unpivot的关系：pivot和unpivot是相反的操作。unpivot将列转换为列值，而pivot则将列值转换为列。

6、简单 pivot 示例

示例表信息,显示2024年每月的v-cut和uv固化，背钻流程工步的过账面积

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 背钻没有 2024-02,2024-03,2024-04 3月的过账面积。

以下代码显示相同的结果，该结果经过透视以使 passover_month 过账月份值成为列标题。

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 /*数据源*/
as p
pivot 
(
    sum(outqty_area/*行转列后 列的值*/) for 
    p.passover_month/*需要行转列的列*/ 
	in ([2024-01],[2024-02],[2024-03],[2024-04],[2024-05]
	/*列的值*/)
) as t

在这里插入图片描述

提供个五列表示2024年前五个月份，因1715 背钻流程工步没有 2024-02,2024-03,2024-04 3月的过账面积，即使结果为 null。

重要提示
如果聚合函数与 pivot 一起使用，则计算聚合时将不考虑出现在值列中的任何空值

7、unpivot

unpivot 逆透视示例
与 pivot 执行的操作几乎相反，unpivot将列转换为列值即多列转换为一列，而pivot则将列值转换为列即一列的多行数据转为多列。

select * from t_passover_unpivot

数据表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

在这里插入图片描述