oracle绝招多行快速转换为列（oracle 中多行转列）

Oracle绝招：多行快速转换为列

在Oracle数据库中，我们经常需要将多行数据转换为一列数据，例如将不同科目的成绩记录在一行，而不是每行只记录一个科目的成绩。本文将介绍一种快速实现多行转列的方法。

例如，有以下成绩表：

ID SUBJECT SCORE

1 Math 80

1 English 70

2 Math 90

2 English 85

2 Physics 75

希望将其转换为以下形式：

ID Math English Physics

1 80 70 –

2 90 85 75

其中，每一列对应一个科目的成绩，如果某个学生没有选择某个科目，则用“-”表示。

实现该功能的关键是使用Oracle的PIVOT语句，它可以将行转为列。其中，PIVOT语句是在SELECT语句上使用的，用于指定需要进行聚合操作的列以及需要作为列的数据。

下面是使用PIVOT语句将成绩表转换为列的代码：

SELECT *

FROM (SELECT id, subject, score FROM scores)

PIVOT (SUM(score) FOR subject IN (‘Math’, ‘English’, ‘Physics’));

在该代码中，先从scores表中选择id、subject和score，然后使用PIVOT语句将subject列转换为列，并对每个学生的每个科目的成绩进行SUM聚合操作。

不过这里有一个小问题，如果有新科目加入，我们需要每次手动添加该科目的名称到PIVOT语句中，这将会带来很多麻烦。因此，我们需要一种自动添加新科目的方法。

我们可以使用Oracle中的动态SQL语句来实现自动添加新科目。具体实现方法如下：

DECLARE

v_sql VARCHAR2(1000);

v_subjects VARCHAR2(1000);

BEGIN

–获取所有科目

SELECT DISTINCT subject INTO v_subjects FROM scores;

–构造动态SQL语句

v_sql :=

‘SELECT * FROM (SELECT id, subject, score FROM scores)

PIVOT (SUM(score) FOR subject IN (‘ || v_subjects || ‘))’;

–执行动态SQL语句

EXECUTE IMMEDIATE v_sql;

END;

在该代码中，先获取所有不重复的科目名称存储到v_subjects变量中，然后使用字符串拼接的方式构造动态SQL语句，使用EXECUTE IMMEDIATE语句将该语句执行。

使用动态SQL语句，无论科目数量多少，都可以快速地将多行数据转换为列。

使用Oracle的PIVOT语句可以实现多行数据快速转换为列，并且使用动态SQL语句可以自动添加新数据。这些技巧在实际数据库开发过程中非常实用，可以使开发效率得到明显提高。