ORACLE行转列的方法

Oracle行转列的方法有多种，以下是其中的一些：

1. 使用PIVOT函数：PIVOT函数是Oracle 11g及以上版本中提供的用于行转列的函数，可以将行数据转换为列数据。例如，将每个部门的员工数量转换为各个部门的列，可以使用以下代码：

SELECT *
FROM (
  SELECT department, employee_count
  FROM employee
)
PIVOT (
  SUM(employee_count)
  FOR department IN ('Sales', 'Marketing', 'Operations')
);

2. 使用CASE语句：CASE语句可以根据条件将行数据转换为列数据。例如，将每个部门的员工数量转换为各个部门的列，可以使用以下代码：

SELECT
  SUM(CASE WHEN department = 'Sales' THEN employee_count ELSE 0 END) AS Sales,
  SUM(CASE WHEN department = 'Marketing' THEN employee_count ELSE 0 END) AS Marketing,
  SUM(CASE WHEN department = 'Operations' THEN employee_count ELSE 0 END) AS Operations
FROM employee;

3. 使用UNPIVOT函数：UNPIVOT函数可以将列数据转换为行数据。例如，将各个部门的员工数量转换为部门和员工数量的行，可以使用以下代码：

SELECT department, employee_count
FROM (
  SELECT 'Sales' AS department, Sales AS employee_count,
         'Marketing' AS department, Marketing AS employee_count,
         'Operations' AS department, Operations AS employee_count
  FROM (
    SELECT
      SUM(CASE WHEN department = 'Sales' THEN employee_count ELSE 0 END) AS Sales,
      SUM(CASE WHEN department = 'Marketing' THEN employee_count ELSE 0 END) AS Marketing,
      SUM(CASE WHEN department = 'Operations' THEN employee_count ELSE 0 END) AS Operations
    FROM employee
  )
)
UNPIVOT (
  employee_count FOR department IN ('Sales', 'Marketing', 'Operations')
);