oracle 数据库的 pivot 函数可将行数据转换为列数据，语法为：pivot (agg_expr for col_name in (value1, value2, …, valuen) [partition by partition_col_name1, …, partition_col_namen])。示例使用场景是将多个行的 sport 列转换为对应列，如 basketball、soccer、baseball。
如何将 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;
输出：
| ID | Basketball | Soccer | Baseball |
|---|---|---|---|
| 1 | John | NULL | NULL |
| 2 | NULL | Mary | NULL |
| 3 | Bob | NULL | NULL |
| 4 | NULL | Jane | NULL |
| 5 | NULL | NULL | Tom |
