pivot unpivot_静态和动态SQL Pivot和Unpivot关系运算符概述

pivot unpivot

In this article, we’ll walk-through the SQL Pivot and SQL Unpivot operators and how they can be useful to transpose SQL Server data. Also, we’ll discuss both static and dynamic ways to use PIVOT and UNPIVOT relational operators that can be used to transform aggregated distinct values as column(s) in the result-set by specifying all the column values in the PIVOT IN clause.

在本文中，我们将逐步介绍SQL Pivot和SQL Unpivot运算符，以及它们如何对转置SQL Server数据有用。 此外，我们还将讨论使用PIVOT和UNPIVOT关系运算符的静态和动态方式，这些运算符可通过在PIVOT IN子句中指定所有列值来将聚合的不同值转换为结果集中的列。

SQL Server枢轴介绍 (SQL Server pivot Introduction)

There are several ways to transpose a dataset from rows to columns and columns to rows. SQL Pivot is one of the techniques that allows transposing of rows to columns and performs possible aggregations along the way. SQL PIVOT and SQL UNPIVOT relational operators transpose a table-valued two-dimensional data into another form of data. SQL PIVOT transposes a table-valued expression from a unique set of values from one column into multiple columns in the output and performs aggregations. SQL UNPIVOT performs the opposite operation of SQL PIVOT by transforming the columns of a table-valued expression into column values.

有几种方法可以将数据集从行到列以及从列到行进行转置。 SQL Pivot是一种允许将行转置为列并在此过程中执行可能的聚合的技术之一。 SQL PIVOT和SQL UNPIVOT关系运算符将表值的二维数据转换为另一种数据形式。 SQL PIVOT将来自一组唯一值的表值表达式转置为输出中的多个列，并执行聚合。 SQL UNPIVOT通过将表值表达式的列转换为列值来执行SQL PIVOT的相反操作。

In most cases, the static pivoting technique suffices the business requirement. For example, the monthly sales forecast, yearly sales split, quarterly sales aggregation, etc, where the IN clause columns remain static. In some other instances, we need granular details and the table-value expression is more dynamic in nature and all the time new set of the expression are included in the table then dynamic PIVOT would be the best choice.

在大多数情况下，静态旋转技术足以满足业务需求。 例如，月度销售预测，年度销售拆分，季度销售汇总等，其中IN子句列保持不变。 在其他一些情况下，我们需要细粒度的细节，并且表值表达式本质上是更动态的，并且表中始终包含新的表达式集，那么动态PIVOT将是最佳选择。

Note: In the above depiction, we can see that in the process of PIVOT, the column values are rotated from vertical to horizontal and UNPIVOTING is like rotating it from horizontal to vertical.

注意：在以上描述中，我们可以看到在PIVOT的过程中，列值从垂直旋转到水平，而UNPIVOTING就像从水平旋转到垂直。

Syntax

句法

SELECT <non-pivoted column>,

SELECT <非枢轴列>，

[pivot_column_1] AS <column alias>,

[pivot_column_1] AS <列别名>，

[pivot_column_2] AS <column alias>,

[pivot_column_2] AS <列别名>，

…

…

[pivot_column_n] AS <column alias>

[pivot_column_n] AS <列别名>

FROM

从

(

（

<SELECT QUERY>)

<选择查询>）

AS <Alias for temporary data se