SQL横纵转换查询|极客教程

SQL横纵转换查询

在实际的数据分析和报告中，我们经常会遇到需要将数据进行横纵转换的情况。横纵转换是指将行数据转换为列数据或将列数据转换为行数据，以便更好地展现数据以及进行分析。在SQL中，我们可以通过一些技巧来实现数据的横纵转换查询。本文将详细介绍如何在SQL中进行横纵转换查询，包括使用CASE WHEN语句、使用PIVOT和UNPIVOT函数等各种方法。

使用CASE WHEN语句进行横纵转换

在SQL中，我们可以使用CASE WHEN语句来进行数据的横纵转换。假设我们有如下的一张表sales_data，存储了不同商品在不同日期的销售额：

CREATE TABLE sales_data (
    product_id INT,
    sale_date DATE,
    sale_amount DECIMAL
);

INSERT INTO sales_data (product_id, sale_date, sale_amount) VALUES
(1, '2022-01-01', 100),
(2, '2022-01-01', 200),
(1, '2022-01-02', 150),
(2, '2022-01-02', 250);

现在我们希望将商品的销售额按日期进行横向展示，即列出每个日期的销售额情况。我们可以使用以下SQL语句来实现这一转换：

SELECT
    MAX(CASE WHEN product_id = 1 THEN sale_amount END) AS product_1_amount,
    MAX(CASE WHEN product_id = 2 THEN sale_amount END) AS product_2_amount,
    sale_date
FROM
    sales_data
GROUP BY
    sale_date;

运行以上SQL语句后，我们将得到如下结果：

product_1_amount	product_2_amount	sale_date
100	200	2022-01-01
150	250	2022-01-02

通过CASE WHEN语句，我们成功将原始的行数据转换为了按日期展示商品销售额的列数据。

使用PIVOT函数进行横纵转换

除了可以使用CASE WHEN语句外，SQL中还提供了PIVOT函数来简化数据的横纵转换过程。PIVOT函数可以将原始的行数据转换为列数据，极大地简化了我们的SQL语句。使用PIVOT函数进行横纵转换的示例代码如下：

SELECT *
FROM
    (
        SELECT product_id, sale_date, sale_amount
        FROM sales_data
    ) AS src
PIVOT
    (
        MAX(sale_amount) FOR product_id IN ([1] AS product_1, [2] AS product_2)
    ) AS pivot_table;

通过以上SQL语句，我们同样可以得到横向展示商品销售额的结果表，如下所示：

sale_date	product_1	product_2
2022-01-01	100	200
2022-01-02	150	250

PIVOT函数通过FOR子句中的商品ID来确定需要转换为列数据的列，将sale_amount字段进行横向展示。

使用UNPIVOT函数进行纵横转换

除了横向转换数据外，有时候我们也需要将列数据转换为行数据，这时可以使用UNPIVOT函数来实现。假设我们有如下的一张表sales_pivot_data，存储了商品销售额的横向展示数据：

CREATE TABLE sales_pivot_data (
    sale_date DATE,
    product_1 DECIMAL,
    product_2 DECIMAL
);

INSERT INTO sales_pivot_data (sale_date, product_1, product_2) VALUES
('2022-01-01', 100, 200),
('2022-01-02', 150, 250);

现在我们希望将商品销售额按日期展示的横向数据转换为列数据，即将列数据转换为行数据。我们可以使用以下SQL语句来实现UNPIVOT：

SELECT
    sale_date,
    product_id,
    sale_amount
FROM
    sales_pivot_data
UNPIVOT
    (
        sale_amount FOR product_id IN (product_1, product_2)
    ) AS unpivot_table;

以上SQL语句将sales_pivot_data表中的横向展示数据纵向转换为列数据，结果如下所示：