unpivot详解

一、unpivot函数

SELECT *
FROM (
        SELECT id, name, [2019-Q1], [2019-Q2], [2019-Q3], [2019-Q4]
        FROM unpivot_test
    ) p
UNPIVOT (
        amount
        FOR quarter IN ([2019-Q1], [2019-Q2], [2019-Q3], [2019-Q4])
    ) AS unpvt;

大家都知道Pivot可以将多行转化为一行，而把一行数据转化为多行数据，可以用到SQL SERVER数据库提供的Unpivot函数。Unpivot函数是一种数据变换方法，特别适用于将水平的列数据转换为垂直的行数据。

Unpivot函数有两个必须的信息：1是要进行Unpivot操作的列名，2是对应列名的数据值所需要创建的目标列，Unpivot SQL语句的语法如下：

SELECT unpivot的目标列, unpivot的参照列, unpivot的值列
FROM unpivot的数据源
UNPIVOT
( unpivot的值列 for unpivot的参照列 in 
     (
         unpivot的数据源的列1, 
         unpivot的数据源的列2,
         …………
     )
) as unpivot别名

二、unpivot函数用法

Unpivot函数中，unpivot的目标列、参照列和值列都是需要我们自己定义的。假设我们有一个极简主义的销售数据表，结构如下：

CREATE TABLE sales (
    region   VARCHAR(20),
    quarter1 FLOAT,
    quarter2 FLOAT,
    quarter3 FLOAT,
    quarter4 FLOAT
);

INSERT INTO sales (region, quarter1, quarter2, quarter3, quarter4)
VALUES ('North', 1000, 1200, 1300, 1100);
INSERT INTO sales (region, quarter1, quarter2, quarter3, quarter4) 
VALUES ('South', 1200, 1300, 1150, 900);

该数据表有一个区域和四个季度的销售数据。如果我们要将这个表中水平的四个季度的销售数据转化为垂直的行数据（unpivot），也即返回一个区域一列、季度一列和销售额一列的表格。可以使用以下SQL语句：

SELECT
    region,
    quarter, 
    sales
FROM
    sales
UNPIVOT
    (
        sales FOR quarter IN 
            (quarter1, quarter2, quarter3, quarter4)
    ) AS unpvt;

三、unpivot用法

在某些情况下，需要将列转换成行。例如，有一个表格，适用于数据仓库技术，其中提供了5种类型的产品，如下所示：

IF OBJECT_ID('tempdb..#product') IS NOT NULL
BEGIN
    DROP TABLE #product
END
GO
CREATE TABLE #product (
    ProductID INT,
    ProductName VARCHAR(50),
    Category1 INT,
    Category2 INT,
    Category3 INT,
    Category4 INT,
    Category5 INT
)
INSERT INTO #product VALUES
(1, 'TV',   12, 13, 14, 6, 8),
(2, 'Phone', 16, 17, 18, NULL, 19),
(3, 'Radio', 21, NULL, 23, 25, 22)

这种数据格式并不便于选择、过滤和处理数据，如果需要将列转换成行，可以使用unpivot函数。unpivot函数的语法是在需要转换的列名后面编写目标列的名称，通过IN引用需要对转换列进行交叉引用的列名或枚举值，可能还需要WHERE子句过滤为空列采用如下方式执行：

SELECT *
FROM #product p
UNPIVOT
    (
        value FOR Category IN 
            (Category1, Category2, Category3, Category4, Category5)
    ) AS unpvt
WHERE value IS NOT NULL
ORDER BY ProductID, Category

四、unpivot怎么读

Unpivot这个单词在英语中是“反转某事物”的意思，也就是把某件事物原本的形态调转过来再展示。在技术领域中，如果想使用unpivot，就需要明白什么样的情况适用于Unpivot函数。

五、unpivot是什么

在SQL服务器中，我们可以使用Unpivot函数将数据由列格式转化为行格式。这个转换可以通过简单地调用系统定义函数操作来实现，为了对Unpivot有一个更好的理解，在此提供一个简短的例子：

SELECT 
       TerritoryID , SalesYear , SalesQuarter , SalesAmount
FROM 
(
    SELECT 
            *
    FROM 
        Sales
) p
UNPIVOT 
(
     SalesAmount FOR SalesQuarter IN ([Q1], [Q2], [Q3], [Q4])
)AS unpvt

六、unpivot 如何使用

下面通过几个例子更详细地解释如何使用Unpivot函数：

例子1：将列旋转成行

    SELECT C.Code , M.Month , S.Value
    FROM   Sales AS T
    UNPIVOT 
    (
        Value FOR Month IN ([Jan], [Feb], [Mar], [Apr], [May], [Jun],
                            [Jul], [Aug], [Sep], [Oct], [Nov], [Dec])
    ) AS S
    INNER JOIN Customers AS C ON T.CustomerID = C.CustomerID
    INNER JOIN Months AS M   ON M.ID = S.Month
    WHERE T.SalesYear = 2019

假设我们的销售表中为每年的销售额指定了12个月份，如上所示。

例子2：需要将一个表连接到另一个表并将其转换为 UNPIVOT 使用的形式。在进行 Unpivot 操作前，将 Union 操作的结果作为一个表返回：

    SELECT Lower(UPPER(Col1),Col2)AS ColName,Value
    FROM T1
    UNPIVOT 
    (
        Value FOR Col2 IN (Col2a,Col2b,Col2c,Col2d,Col2e)
    ) AS u1;

例子3：Unpivot的目标列、参照列和值列都是需要我们自己定义的。

    SELECT
        region,
        quarter, 
        sales
    FROM
        sales
    UNPIVOT
        (
            sales FOR quarter IN 
                (quarter1, quarter2, quarter3, quarter4)
        ) AS unpvt;

七、unpivot函数使用

在使用Unpivot时需要注意以下几点：

Unpivot只适用于关系型数据库
Unpivot只适用于两个及以上列，对于只含有一列的表也没有进行任何变化的必要
在Unpivot时目标列名和联合Unpivot的列宽度应该一致，否则可能会变成空值或者无效值

八、unpivot与pivot区别

Pivot和Unpivot操作是互逆的。 Pivot 操作可以将行和列互换，而 Unpivot 操作则同时涉及列值和列名。

在 SQL language 中，Pivot 查询可以将多个列字段值透视为单个结果行，而通常情况下，Unpivot 查询可以做相反，将一个用于类别分组的列分割为多个行

原创文章，作者：小蓝，如若转载，请注明出处：https://www.506064.com/n/195882.html