oracle sql枢轴和unpivot运算符解释

如何在Oracle SQL Server中将行旋转到列？(How do you Pivot rows to columns in Oracle SQL Server?)

There are several methods to convert multiple rows to columns in SQL query.

有几种方法可以将多行转换为SQL查询中的列。

You can easily transform the data with or without aggregate using a pivot function which is one of the simplest option for transposing rows to columns. Let’s summarize how to use the PIVOT operator:

您可以使用数据透视功能轻松地转换数据，无论有无聚合，这都是将行转换为列的最简单的选择之一。 让我们总结一下如何使用PIVOT运算符：

First up, here are some quick table definitions and data for use.

首先，这里是一些快速的表定义和要使用的数据。

We will use CREATE TABLE statement to create a new table in a database for this example:

对于此示例，我们将使用CREATE TABLE语句在数据库中创建一个新表：

CREATE TABLE 
(
  
  
  
);

After completing this step, we need to use following SQL statement to insert new records in the table:

完成此步骤后，我们需要使用以下SQL语句在表中插入新记录：

INSERT INTO 
(
  
  
)
VALUES
    (2017, 1, 16),
    (2017, 2, 12),
    (2017, 3, 19),
    (2017, 4, 22),
    (2017, 5, 27),
    (2017, 6, 30);

We now have a Temperatures table. We will say that column 1 is a Year number, and column 2 is a Month number and lastly column 3 is the average temperature of days in a month.

现在，我们有一个温度表。 我们将说第1列是Year编号，第2列是Month编号，最后第3列是一个月中几天的平均温度。

Temperatures table will look like this:

温度表如下所示：

YEAR_NUM   MONTH_NUM   AVG_TEMP       
    2017           1         16    
    2017           2         12
    2017           3         19
    2017           4         22
    2017           5         27
    2017           6         30

I would like it to come out as a pivot table, like this:

我希望将其作为数据透视表发布，如下所示：

YEAR_NUM     1     2     3     4     5     6       
    2017    16    12    19    22    27    30

YEAR_NUM numbers down the side and MONTH_NUM across the top.

YEAR_NUM个数字位于下方，而MONTH_NUM个则位于上方。

As you can see below, Script 1 shows how a PIVOToperator can be utilized.

如下所示，脚本1显示了如何利用PIVOT运算符。

Script 1

脚本1

SELECT * 
FROM Temperatures
PIVOT
    (
    sum(avg_temp)
    FOR month_num IN (1,2,3,4,5,6)
    );

The result of executing Script 1 is exactly similar to that of Table which I wanted to get.

执行脚本1的结果与我想要获取的Table的结果完全相似。

Oracle SQL Server UNPIVOT查询简介，该查询可将列转换回行。 (Introduction to Oracle SQL Server UNPIVOT query that converts columns back to rows.)

Now you will learn how to write an Oracle UNPIVOT query to transpose data from multiple columns to rows.

现在，您将学习如何编写Oracle UNPIVOT查询以将数据从多列转置为行。

UNPIVOT clause performs the opposite operation to PIVOT by turning columns of a table into rows. Obtaining the original data by reversing a PIVOT statement refers to the process of applying the UNPIVOT function to the dataset which is already PIVOTED in SQL Server.

UNPIVOT子句通过将表的列转换为行来执行与PIVOT相反的操作。 通过反转PIVOT语句获取原始数据是指将UNPIVOT函数应用于SQL Server中已经PIVOTED的数据集的过程。

To give another example for using UNPIVOT function, the following code produces a seven-column table that has one row.

再举一个使用UNPIVOT函数的例子，下面的代码产生了一个七行的表，其中有一行。

>SQL CREATE TABLE Statement
CREATE TABLE Temperatures
(
  YEAR_NUM int, 
  Jan int, 
  Feb int,
  Mar int,
  Apr int,
  May int,
  Jun int
);>SQL INSERT INTO Statement
INSERT INTO Temperatures
(
  YEAR_NUM, 
  Jan, Feb, Mar,Apr, May, Jun
)
VALUES
    (2017, 16, 12, 19, 22, 27, 30);

New Temperatures table will now look like this:

现在，“新温度”表将如下所示：

YEAR_NUM   JAN   FEB   MAR   APR   MAY   JUN       
    2017    16    12    19    22    27    30

We have created a new table and insert some data into the table to apply UNPIVOT operator. You may use your own database tables.

我们创建了一个新表，并将一些数据插入到表中以应用UNPIVOT运算符。 您可以使用自己的数据库表。

The following syntax summarizes how to use Oracle SQL UNPIVOT operator.

以下语法总结了如何使用Oracle SQL UNPIVOT运算符。

SELECT * 
FROM
    (
    SELECT *
    FROM Temperatures
    UNPIVOT(
        avg_temp
        FOR MONTH_NUM
        IN (Jan, Feb, Mar, Apr, May, Jun))
    );

Here is the result set.

这是结果集。

YEAR_NUM   MONTH_NUM   AVG_TEMP       
    2017         JAN         16    
    2017         FEB         12
    2017         MAR         19
    2017         APR         22
    2017         MAY         27
    2017         JUN         30

I have explained how to write PIVOT and UNPIVOT operators to convert a table so far.

到目前为止，我已经解释了如何编写PIVOT和UNPIVOT运算符来转换表。

You can perform PIVOT and UNPIVOT operations on same table in single query. If PIVOT carries out an aggregation and merges multiple rows into a row in the output table, then UNPIVOT cannot retrieve the original table from the output because rows have been merged. So, the conclusion is that UNPIVOT function is not the exact reverse of PIVOT function.

您可以在单个查询中对同一表执行PIVOT和UNPIVOT操作。 如果PIVOT执行聚合并将多行合并到输出表中的一行中，则UNPIVOT无法从输出中检索原始表，因为行已合并。 因此，结论是UNPIVOT功能与PIVOT功能并非完全相反。

Here is an example to apply the UNPIVOT operator to pivoted data.

这是将UNPIVOT运算符应用于 数据透视。

Let’s look at the table we used in this example:

让我们看一下本示例中使用的表：

EmployeeName   Month   Salary       
Sally         Jan      3500   
Sally         Feb      3750
Edward        Jan      5500
Edward        Feb      5500

We can execute this query on the database:

我们可以在数据库上执行以下查询：

SELECT EmployeeName, Month, Salary
FROM
(SELECT * FROM
 
(SELECT 
	EmployeeName,
	Salary,
	Month
FROM 
	Table
)
AS EmployeeTable
PIVOT(
	SUM(Salary)
	FOR Month IN ([Jan],[Feb])
) AS EmployeePivot) PivotedResults
UNPIVOT
(
	Salary
	FOR Month in (Jan, Feb)
) AS EmployeeUnpivot

Since it is an non-aggregated pivot table, you will get the original table in the output.

由于它是一个非聚合的数据透视表，因此您将在输出中获得原始表。

That’s it.

而已。

Stay connected for more tutorials and use cases.

保持联系以获取更多教程和用例。

翻译自: https://medium.com/@firuze.koyuncu/oracle-sql-pivot-and-unpivot-operators-explained-22d3852281c2