Oracle一行数据转换为多列解决方案（oracle一行转为多列）

Oracle一行数据转换为多列 解决方案

在处理Oracle数据库中的数据时，经常会遇到需要将一行数据转换为多列的情况。例如，某个表中的数据如下：

| ID | NAME | VALUE |

|—-|——|——-|

| 1 | A | 100 |

| 1 | B | 200 |

| 1 | C | 300 |

| 2 | D | 400 |

| 2 | E | 500 |

| 2 | F | 600 |

需要将其转换为如下格式：

| ID | NAME1 | VALUE1 | NAME2 | VALUE2 | NAME3 | VALUE3 |

|—-|——-|——–|——-|——–|——-|——–|

| 1 | A | 100 | B | 200 | C | 300 |

| 2 | D | 400 | E | 500 | F | 600 |

虽然该问题看起来很棘手，但实际上可以通过SQL的PIVOT和UNPIVOT语句来解决。

下面是一份简单的示例代码：

WITH data AS (
  SELECT 1 AS ID, 'A' AS NAME, 100 AS VALUE FROM DUAL UNION ALL
  SELECT 1 AS ID, 'B' AS NAME, 200 AS VALUE FROM DUAL UNION ALL
  SELECT 1 AS ID, 'C' AS NAME, 300 AS VALUE FROM DUAL UNION ALL
  SELECT 2 AS ID, 'D' AS NAME, 400 AS VALUE FROM DUAL UNION ALL
  SELECT 2 AS ID, 'E' AS NAME, 500 AS VALUE FROM DUAL UNION ALL
  SELECT 2 AS ID, 'F' AS NAME, 600 AS VALUE FROM DUAL
),
pivoted_data AS (
  SELECT *
    FROM data
  PIVOT (
    MAX(NAME) AS NAME, MAX(VALUE) AS VALUE
    FOR NAME IN ('A' AS NAME1, 'B' AS NAME2, 'C' AS NAME3, 'D' AS NAME4, 'E' AS NAME5, 'F' AS NAME6)
  )
)
SELECT *
  FROM pivoted_data
UNPIVOT (
  (NAME1, VALUE1, NAME2, VALUE2, NAME3, VALUE3, NAME4, VALUE4, NAME5, VALUE5, NAME6, VALUE6)
  FOR NAME IN (NAME1, VALUE1, NAME2, VALUE2, NAME3, VALUE3, NAME4, VALUE4, NAME5, VALUE5, NAME6, VALUE6)
);

该查询的第一步是使用WITH子句创建名为data的虚拟表，其中包含了示例数据。接下来，使用PIVOT语句将数据从行转换为列。在本例中，将每个NAME和对应的VALUE作为对应ID的列。然后，使用UNPIVOT语句再次将数据从列转换为行，以获得所需的结果。

以上就是Oracle一行数据转换为多列的解决方案，使用PIVOT和UNPIVOT语句可以轻松地实现此任务。