Oracle数据库行列转换全攻略

"数据库行列转换算法的总结，涵盖了Oracle数据库中的列转行、行转列、多列转字符串、多行转字符串、字符串转多列和字符串转多行六种情况，适用于8i, 9i, 10g及更高版本。文中提到的转换方法包括UNION ALL和MODEL子句，并提供了示例代码。" 在Oracle数据库中，行列转换是数据处理和分析的重要部分。以下是对这些转换方法的详细解释： 1. 列转行 - UNION ALL 方法：适用于所有支持UNION ALL的Oracle版本。通过将每一列的数据用UNION ALL连接起来，转换为行。例如，将表`t_col_row`的c1、c2、c3三列转换为行，可以使用如下SQL： ```sql SELECT id, 'c1' cn, c1 cv FROM t_col_row UNION ALL SELECT id, 'c2' cn, c2 cv FROM t_col_row UNION ALL SELECT id, 'c3' cn, c3 cv FROM t_col_row; ``` - MODEL 子句：在Oracle 10g及以上版本中可用。它提供更复杂的行/列转换能力，可以处理更复杂的转换逻辑，如： ```sql SELECT id, cn, cv FROM t_col_row MODEL RETURN UPDATED ROWS PARTITION BY (id) DIMENSION BY (ROW_NUMBER() OVER (ORDER BY NULL) AS rn) MEASURES (c1, c2, c3 AS cv, 'c1' AS cn) RULES ( cv[FOR rn FROM 1 TO 1 WHEN cn = 'c1'] = c1, cv[FOR rn FROM 2 TO 2 WHEN cn = 'c2'] = c2, cv[FOR rn FROM 3 TO 3 WHEN cn = 'c3'] = c3 ); ``` 2. 行转列 - 这通常涉及到聚合函数，如PIVOT，不过示例未给出具体实现。 3. 多列转换成字符串 - 可以使用`LISTAGG`函数（11g及以上版本）或自定义方法，如在10g及以下版本使用`WM_CONCAT`等。 4. 多行转换成字符串 - 可以结合`LISTAGG`或`WM_CONCAT`，以及分组来实现。 5. 字符串转换成多列 - 常用方法是使用`REGEXP_SUBSTR`配合正则表达式进行分割。 6. 字符串转换成多行 - 可以利用`CONNECT BY`层次查询，或者`REGEXP_COUNT`结合`REGEXP_SUBSTR`来实现。 在实际应用中，选择哪种方法取决于具体需求和数据库版本。对于较早版本的Oracle，可能需要依赖一些特定的函数或技巧，而在新版本中，Oracle提供了更强大的内置功能，如PIVOT和UNPIVOT，使得行列转换更为便捷。在处理大量数据时，还需考虑性能和可读性。