Oracle数据库实战：横表到纵表的高效转换技术详解

引言

        在数据分析和数据处理的过程中，我们经常遇到需要将横表结构的数据转换为纵表结构的需求，这种转换过程通常被称为“行列转换”。在Oracle数据库环境下，实现这一目标既可以通过SQL查询语句直接实现，也可以借助于PL/SQL存储过程等高级功能。

Oracle数据库实战：一键执行批量将横表转纵表（横向表转纵向表） 【→→→戳我跳转→】

一、横表与纵表的定义

        横表是指多列记录同一类属性的不同取值，而纵表则是将这类属性及其对应的取值展开为多行记录。例如，一张横表可能包含员工ID、项目1成绩、项目2成绩等多个字段，而在纵表中，每个项目及对应成绩会被拆分为独立的行记录。

二、Oracle SQL中的Pivot与Unpivot操作

1. Pivot操作

创建横表DDL和初始化数据DML

-- 创建横表 Employee_Projects     CSND-小小野猪
CREATE TABLE Employee_Projects (
    EmployeeID VARCHAR2(10),
    ProjectName VARCHAR2(30),
    Score NUMBER
);

-- 插入示例数据  CSND-小小野猪
INSERT INTO Employee_Projects VALUES ('Emp1', 'ProjectA', 85);
INSERT INTO Employee_Projects VALUES ('Emp1', 'ProjectB', 90);
INSERT INTO Employee_Projects VALUES ('Emp2', 'ProjectA', 92);
INSERT INTO Employee_Projects VALUES ('Emp3', 'ProjectA', 95);
INSERT INTO Employee_Projects VALUES ('Emp3', 'ProjectB', 88);
INSERT INTO Employee_Projects VALUES ('Emp3', 'ProjectC', 93);

COMMIT;

Oracle SQL从11g版本开始引入了Pivot关键字，用于将横表转换为纵表。其基本语法如下：

-- CSND-小小野猪
SELECT *
FROM your_table
PIVOT (
    AGGREGATE_FUNCTION(your_column)
    FOR pivot_column IN (value1, value2, ... valueN)
);

例如，假设有一个横表Employee_Projects，包含EmployeeID, ProjectName, Score三列，我们可以将其转换为每个员工每项项目的得分单独列为一行：

-- CSND-小小野猪
WITH Employee_Projects AS (
    SELECT 'Emp1' AS EmployeeID, 'ProjectA' AS ProjectName, 85 AS Score FROM DUAL
    UNION ALL
    SELECT 'Emp1', 'ProjectB', 90 FROM DUAL
    UNION ALL
    SELECT 'Emp2', 'ProjectA', 92 FROM DUAL
)
SELECT *
FROM Employee_Projects
PIVOT (
    MAX(Score)
    FOR ProjectName IN ('ProjectA' AS ProjectA_Score, 'ProjectB' AS ProjectB_Score)
);

2. Unpivot操作

创建纵表DDL和初始化DML语句

-- 创建一个虚拟的纵表数据集，这里只是为了演示Unpivot操作  CSND-小小野猪
CREATE TABLE VerticalTable AS
SELECT 'Emp1' AS EmpID, 'ProjectA' AS Attribute, 85 AS Value FROM DUAL UNION ALL
SELECT 'Emp1', 'ProjectB', 90 FROM DUAL UNION ALL
SELECT 'Emp2', 'ProjectA', 92 FROM DUAL UNION ALL
SELECT 'Emp3', 'ProjectA', 95 FROM DUAL UNION ALL
SELECT 'Emp3', 'ProjectB', 88 FROM DUAL UNION ALL
SELECT 'Emp3', 'ProjectC', 93 FROM DUAL;

COMMIT;

-- 使用Unpivot将纵表转为横表
SELECT EmpID, ProjectName, Score
FROM (
    SELECT EmpID, Attribute, Value
    FROM VerticalTable
)
UNPIVOT (
    Score FOR ProjectName IN (Attribute)
);

将纵表转为横表时，使用Unpivot操作，其基本语法如下：

-- CSND-小小野猪
SELECT *
FROM your_table
UNPIVOT (
    unpivoted_value
    FOR unpivot_column IN (column1, column2, ... columnN)
);

三、动态Pivot技术

对于不固定数量的项目，静态Pivot可能无法满足需求，此时可以采用动态SQL构建Pivot语句。这通常涉及到使用DBMS_SQL包或者EXECUTE IMMEDIATE命令构造并执行动态SQL。

-- CSND-小小野猪
DECLARE
  v_sql VARCHAR2(4000);
BEGIN
  -- 构建动态Pivot SQL语句
  v_sql := 'SELECT * FROM Employee_Projects
            PIVOT(MAX(Score) 
                 FOR ProjectName IN (' || list_of_projects || '))';   -- 这里的list_of_projects是表中多个的ProjectName

  -- 使用EXECUTE IMMEDIATE执行动态SQL
  EXECUTE IMMEDIATE v_sql;
END;

其中，list_of_projects需要提前获取并拼接成逗号分隔的字符串形式。

四、利用CASE表达式手动实现行列转换

        Oracle数据库实战：一键执行批量将横表转纵表（横向表转纵向表） 【→→→戳我跳转→】

在不支持Pivot操作的老版本Oracle数据库或特定场景下，可以采用CASE WHEN搭配GROUP BY的方式手动模拟Pivot效果：

-- CSND-小小野猪
SELECT EmployeeID,
       MAX(CASE WHEN ProjectName = 'ProjectA' THEN Score END) AS ProjectA_Score,
       MAX(CASE WHEN ProjectName = 'ProjectB' THEN Score END) AS ProjectB_Score
FROM Employee_Projects
GROUP BY EmployeeID;

五、利用存储过程实现横表转纵表

        Oracle数据库实战：一键执行批量将横表转纵表（横向表转纵向表） 【→→→戳我跳转→】

六、性能优化建议

• 对于大数据量的行列转换操作，合理运用索引、分区等数据库优化技术可显著提高转换效率。
• 尽量避免在转换过程中进行不必要的全表扫描，尽量减少中间临时表的使用。
• 在进行动态Pivot时，注意SQL注入风险，并做好必要的变量清理和绑定工作。

结语

        横表到纵表的转换是数据库处理中的常见任务，Oracle提供了丰富的原生功能以及灵活的方法来实现这一目标。熟练掌握这些技巧有助于提升数据处理能力和效率，进而更好地服务于数据分析和报表生成等工作场景。

声明

本内容版权归属于CSDN-小小野猪，任何未经授权的复制、转载、传播、贩卖、转赠等均属违法行为，必将追究法律责任！！！