oracle怎么转换为列-Oracle

admin2024-06-05基础运维知识100

如何将 Oracle 数据转换成列

Oracle 数据库提供了一个名为 PIVOT 的函数，它可以将行数据转换为列数据。PIVOT 函数的语法如下：

PIVOT (
    agg_expr
    FOR col_name
    IN (value1, value2, ..., valueN)
    [PARTITION BY partition_col_name1, ..., partition_col_nameN]
)

登录后复制

示例：

将以下表中的行数据转换为列数据：

ID	Name	Sport
1	John	Basketball
2	Mary	Soccer
3	Bob	Basketball
4	Jane	Soccer
5	Tom	Baseball

使用 PIVOT 函数将其转换为列数据：

SELECT ID,
       MAX(CASE WHEN Sport = 'Basketball' THEN Name END) AS Basketball,
       MAX(CASE WHEN Sport = 'Soccer' THEN Name END) AS Soccer,
       MAX(CASE WHEN Sport = 'Baseball' THEN Name END) AS Baseball
FROM (
    SELECT ID, Name, Sport
    FROM table_name
)
PIVOT (
    Name
    FOR Sport
    IN ('Basketball', 'Soccer', 'Baseball')
)
GROUP BY ID;

登录后复制

输出：