oracle把列转多列

如何用Oracle将列转换为多列

当我们在 Oracle 中查询数据时，常常会遇到需要将一列数据根据某个参数拆分成多列的情况。这时候就需要用到“列转多列”这个技术。

什么是列转多列

列转多列（Pivot）是指将一列数据按照某个条件拆分成多个列，每个新列代表原先一列中的一个取值。例如，我们有一个订单表，其中一个字段是“订单状态”，可能的取值包括“已下单”、“已付款”、“已发货”、“已完成”等，我们想对每个状态进行统计，就需要将“订单状态”这个字段拆分成多列，“已下单”一列、 “已付款”一列，以此类推。

使用Oracle的PIVOT函数进行列转多列

在Oracle中进行列转多列操作需要使用PIVOT函数。

    SELECT *
    FROM (SELECT col1, col2, col3
          FROM table_name)
    PIVOT (COUNT(col3) FOR col2 IN ('val1' AS alias1, 'val2' AS alias2, ...))

在上述代码中：

SELECT col1, col2, col3 FROM table_name是以col1为主键，查询需要进行列转换的数据。

PIVOT (COUNT(col3) FOR col2 IN ('val1' AS alias1, 'val2' AS alias2, ...))中的COUNT表示对col3实现聚合操作，FOR词后面表示进行拆分的列，IN后面跟的是需要拆分成的新列的列名（当然，也可以使用别名），以AS连接。

Pivot函数的实例：将订单状态拆分成多列

下面我们以一个订单表为例，将其中的订单状态拆分成“已下单”、“已发货”、“已完成”三列。

    CREATE TABLE orders (
        id NUMBER,
        order_status VARCHAR2(10),
        order_amount NUMBER
    );
    INSERT INTO orders VALUES (1, '已下单', 20);
    INSERT INTO orders VALUES (2, '已下单', 30);
    INSERT INTO orders VALUES (3, '已发货', 40);
    INSERT INTO orders VALUES (4, '已完成', 50);
    INSERT INTO orders VALUES (5, '已完成', 60);
    INSERT INTO orders VALUES (6, '已完成', 70);

接下来，我们使用PIVOT函数，将“订单状态”这一列转换成三列：已下单、已发货和已完成。

    SELECT *
    FROM (SELECT id, order_amount, order_status
          FROM orders)
    PIVOT (SUM(order_amount) FOR order_status IN
        ('已下单' AS "已下单", '已发货' AS "已发货", '已完成' AS "已完成"))

执行以上代码即可得到下面的结果：

使用Oracle的CASE函数进行列转多列

除了使用PIVOT函数，我们也可以使用CASE函数，在SELECT语句中手动转换列。

Case函数的语法和实例

CASE函数的语法：

    SELECT
        ...
        CASE
            WHEN condition1 THEN expression1
            WHEN condition2 THEN expression2
            ...
            ELSE expression
        END AS alias
        ...
    FROM table_name;

在上述代码中：

condition1表示第一个条件，会根据这个条件判断，如果成立，就算作expression1。

expression1如果第一个条件判断成立，那么返回的值。

alias表示新列的别名。

比如下面这个例子，我们使用CASE函数将订单状态拆分成三列。

    SELECT
        id,
        SUM(CASE WHEN order_status = '已下单' THEN order_amount ELSE 0 END) AS '已下单',
        SUM(CASE WHEN order_status = '已发货' THEN order_amount ELSE 0 END) AS '已发货',
        SUM(CASE WHEN order_status = '已完成' THEN order_amount ELSE 0 END) AS '已完成'
    FROM orders
    GROUP BY id;

执行以上代码即可得到下面的结果：

总结

在Oracle中进行列转多列操作，有两种方法：使用PIVOT函数和CASE函数。如果数据量很大，使用PIVOT函数会更加高效；如果数据量不大，使用CASE函数更为简便。大家可以根据实际情况选择合适的方法。