Oracle的列转行问题

Oracle中使用语句将行数据转换称不同的列表示，或者将不同的列数据写到同一列的不同行上的行列转换问题是一个非常传统的话题。 网络上流传了很多将行数据转换称列数据的方法和应用实例，一般通过decode或者case函数与聚合函数联合实现功能，这里就不再重复。 日前本人在一个偶然的应用中用到了一个需要将列数据转换为行数据的问题。搜索了很久没有发现很合适的方法。网络一般推荐使用union all实现。这样的一个重要问题在于会造成对数据表的重复访问，性能是个重要问题。 基于此，作者设想了另外一种实现可能性。实践证明，这种方法有效提供了查询性能。 在Oracle数据库中，行列转换是数据处理中常见的需求，尤其在数据分析、报表生成或界面展示时。列转行（Pivot）和行转列（Unpivot）是两种基本操作，可以将数据按照不同的维度进行组织。传统的列转行方法通常涉及`DECODE`或`CASE`函数与聚合函数如`MAX`或`MIN`的结合，但这些方法可能在处理大量数据时对性能造成影响。 例如，在描述中提到的具体案例中，我们有一个名为`f_distribution`的表，包含三个数量字段`qty1`, `qty2`, `qty3`，以及一个标识字段`f1`。要将这些列数据转换为行，传统的做法是使用`UNION ALL`来合并多条SELECT语句，但这种方法会导致多次访问数据，从而影响性能。 作者提出了一个创新的解决方案，利用`DECODE`函数配合子查询来实现列转行，以提高查询效率。创建一个临时的子查询（b），这个子查询生成了代表列名的`fid`值，即'数据 1', '数据 2', '数据 3'。然后，将这个子查询与原始表`f_distribution`（a）进行连接。在`DECODE`函数中，根据`fid`的值选取对应的`qty1`, `qty2`, `qty3`中的值。这样，一行数据被拆分为多行，实现了列转行的效果。 具体的SQL语句如下： ```sql SELECT a.f1, b.fid, DECODE(b.fid, '数据 1', a.qty1, '数据 2', a.qty2, '数据 3', a.qty3) FROM f_distribution a, (SELECT '数据 1' fid FROM dual UNION ALL SELECT '数据 2' fid FROM dual UNION ALL SELECT '数据 3' fid FROM dual) b; ``` 这个查询将`f_distribution`表中的每一行根据`fid`的值转换成三行，每行包含一个`f1`和对应的`qty`值。这种方式避免了`UNION ALL`的多次扫描，提高了查询效率。 当然，Oracle数据库从11g版本开始提供了更高级的`PIVOT`和`UNPIVOT`操作，它们是专为行列转换设计的内置功能，使用起来更为简洁且高效。例如，使用`PIVOT`对上述案例进行列转行的代码如下： ```sql SELECT * FROM f_distribution PIVOT ( SUM(qty) FOR fid IN ('数据 1' AS qty1, '数据 2' AS qty2, '数据 3' AS qty3) ); ``` 这将自动把`f_distribution`的`qty1`, `qty2`, `qty3`列转为行，并计算每个`f1`对应`fid`的总和。 总结来说，Oracle中的列转行可以通过多种方法实现，包括传统的`DECODE`或`CASE`结合子查询，以及从11g版本开始提供的`PIVOT`功能。选择哪种方法取决于具体的需求和数据规模，以及对性能的考虑。在处理大数据量时，应优先考虑使用内置的`PIVOT`功能，以获得更好的性能和可维护性。