oracle多行轉(zhuǎn)一行多列怎么實(shí)現(xiàn)

小億
420
2024-04-17 19:23:06
欄目: 云計(jì)算

在 Oracle 中將多行數(shù)據(jù)轉(zhuǎn)換為一行多列可以使用 LISTAGG 函數(shù)或者 PIVOT 操作。

  1. 使用 LISTAGG 函數(shù)：
SELECT LISTAGG(column_name, ', ') WITHIN GROUP (ORDER BY column_order) AS concatenated_values
FROM table_name;

例如，將 table_name 中的 column_name 列的多行數(shù)據(jù)轉(zhuǎn)換為一行多列數(shù)據(jù)：

SELECT LISTAGG(column_name, ', ') WITHIN GROUP (ORDER BY column_order) AS concatenated_values
FROM table_name;
  1. 使用 PIVOT 操作：
SELECT *
FROM (
  SELECT column_name, column_value
  FROM table_name
)
PIVOT (
  MAX(column_value)
  FOR column_name IN ('value1' AS value1, 'value2' AS value2, 'value3' AS value3)
);

例如，將 table_name 中的 column_name 列的多行數(shù)據(jù)轉(zhuǎn)換為一行多列數(shù)據(jù)：

SELECT *
FROM (
  SELECT column_name, column_value
  FROM table_name
)
PIVOT (
  MAX(column_value)
  FOR column_name IN ('value1' AS value1, 'value2' AS value2, 'value3' AS value3)
);

在以上示例中，根據(jù)實(shí)際情況修改 table_name、column_name 和 column_value 等參數(shù)即可將多行數(shù)據(jù)轉(zhuǎn)換為一行多列數(shù)據(jù)。

0 踩