Oracle行列转换全攻略

"Oracle 行列转换总结" 在Oracle数据库中，进行行列转换是数据分析和报表制作时常见的需求。本文将详细介绍六种不同的转换方法，包括列转行、行转列、多列转换成字符串、多行转换成字符串、字符串转换成多列以及字符串转换成多行。 1. **列转行** 列转行通常用于将表格的一列数据拆分为多行展示。例如，可以使用`UNION ALL`操作实现。在示例中创建了一个表`t_col_row`，包含三列数据c1、c2和c3。通过`UNION ALL`将每一列的数据分别作为新列（cn）和值（cv）插入结果集。如果不需要转换空值，可以通过添加`WHERE COLUMN IS NOT NULL`条件来过滤。 另一种列转行的方法是使用`MODEL`子句，这是10g及以上版本支持的功能。它允许创建一个模型并定义规则，将数据按照指定方式转换。在示例中，`MODEL`子句根据ID分组，定义了三个维度（n=1,2,3）对应三个列名（cn=c1, c2, c3），并设置测量值（cv）等于对应的原始列值。 2. **行转列** 行转列通常用于将多行数据合并到单个单元格中。Oracle提供了`PIVOT`功能来实现这一转换，它可以将特定列的值作为新列名，而其他列的值作为这些新列的值。例如，可以通过`PIVOT`将示例中的数据按ID分组，并将c1、c2、c3转换为以列名命名的新列。 3. **多列转换成字符串** 当需要将多列数据组合成一个字符串时，可以使用`LISTAGG`函数。这个函数在11g及更高版本中引入，可以将一列数据按照指定分隔符连接起来。例如，将`t_col_row`的c1、c2、c3列用逗号分隔，可以写成`SELECT id, LISTAGG(c1, ',') WITHIN GROUP (ORDER BY c1) AS col_string FROM t_col_row`。 4. **多行转换成字符串** 对于多行数据的字符串化，可以结合`LISTAGG`和`GROUP BY`语句。例如，可以将每个ID的所有c1、c2、c3组合成一行字符串。 5. **字符串转换成多列** 将字符串分解成多列通常涉及使用`SUBSTR`和`INSTR`函数配合使用，或者在10g及以上版本中使用`REGEXP_SUBSTR`。假设有一个包含逗号分隔值的字符串列，可以通过提取子字符串将其转换为多个列。 6. **字符串转换成多行** 类似地，如果字符串中的值由换行符分隔，可以使用`REGEXP_REPLACE`和`TRIM`函数将每个值分离成单独的行。 Oracle提供了多种工具和函数来处理行列转换，满足各种复杂的数据处理需求。在实际应用中，应根据具体场景选择合适的方法，同时注意不同转换方法的版本兼容性和性能考虑。了解和熟练掌握这些转换技巧，将极大地提升数据处理和分析的能力。