Oracle行列转换_总结

### Oracle 行列转换知识点详解 #### 一、概述 在数据库操作中，有时需要将数据从行格式转换为列格式或反之亦然，这种需求通常被称为“行列转换”。Oracle 提供了多种方法来实现这样的转换，适用于不同的场景。本文档主要介绍了六种常见的行列转换方法，并通过具体的示例来阐述每一种方法的应用。 #### 二、列转行 列转行是指将表中的多列数据转换为单列多行的形式。这一操作在某些分析场景下非常有用，例如，当你需要展示每个记录的不同属性时。 **1. UNION ALL 方法** - **适用版本**: Oracle 8i 及以上 - **操作原理**: 使用 `UNION ALL` 将每一列的数据连接起来。 - **示例**: ```sql SELECT id, 'c1' AS cn, c1 AS cv FROM t_col_row UNION ALL SELECT id, 'c2' AS cn, c2 AS cv FROM t_col_row UNION ALL SELECT id, 'c3' AS cn, c3 AS cv FROM t_col_row; ``` - **注意事项**: 如果不需要转换空值，可以在每个 `SELECT` 语句后添加 `WHERE COLUMN IS NOT NULL`。 **2. MODEL 子句** - **适用版本**: Oracle 10g 及以上 - **操作原理**: 利用 `MODEL` 子句进行行列转换。 - **示例**: ```sql SELECT id, cn, cv FROM t_col_row MODEL RETURN UPDATED ROWS PARTITION BY (ID) DIMENSION BY (0 AS n) MEASURES ('xx' AS cn, 'yyy' AS cv, c1, c2, c3) RULES UPSERT ALL ( cn[1] = 'c1', cn[2] = 'c2', cn[3] = 'c3', cv[1] = c1[0], cv[2] = c2[0], cv[3] = c3[0] ) ORDER BY ID, cn; ``` **3. Collection 方法** - **适用版本**: Oracle 8i 及以上 - **操作原理**: 使用自定义类型和集合来存储数据，然后通过 `TABLE` 函数展开集合。 - **示例**: ```sql CREATE TYPE cv_pair AS OBJECT(cn VARCHAR2(10), cv VARCHAR2(10)); CREATE TYPE cv_varr AS VARRAY(8) OF cv_pair; SELECT id, t.cn AS cn, t.cv AS cv FROM t_col_row, TABLE(cv_varr(cv_pair('c1', t_col_row.c1), cv_pair('c2', t_col_row.c2), cv_pair('c3', t_col_row.c3))) t ORDER BY 1, 2; ``` #### 三、行转列 行转列是指将多行数据转换为一行多列的形式。这种转换在汇总或分组数据时特别有用。 **1. AGGREGATE FUNCTION 方法** - **适用版本**: Oracle 8i 及以上 - **操作原理**: 使用 `MAX` 函数（或其他聚合函数）结合 `DECODE` 函数来选择特定的行数据。 - **示例**: ```sql SELECT id, MAX(DECODE(cn, 'c1', cv, NULL)) AS c1, MAX(DECODE(cn, 'c2', cv, NULL)) AS c2, MAX(DECODE(cn, 'c3', cv, NULL)) AS c3 FROM t_row_col GROUP BY id ORDER BY 1; ``` - **注意事项**: 在上述示例中，`DECODE` 函数用于判断列名并返回对应的值，如果列名不匹配，则返回 `NULL`。 **额外知识点**: `SIGN()` 函数可以用来根据某个值是否为零、正数或负数来返回不同的结果。此外，还可以使用 `DECODE` 函数与 `SIGN()` 结合来比较字段大小，如: ```sql SELECT DECODE(SIGN(字段1 - 字段2), -1, 字段3, 字段4) FROM dual; ``` 这可以帮助在没有 `CASE WHEN` 的情况下执行简单的条件判断。 #### 四、其他转换类型 除了上述两种基本的行列转换外，还有其他几种转换类型，包括多列到字符串、多行到字符串、字符串到多列以及字符串到多行的转换。这些转换通常涉及对数据进行重新组织或重组，以便于后续的处理或展示。 - **多列转换成字符串** - 可以通过连接多列数据来实现。 - **多行转换成字符串** - 使用聚合函数（如 `LISTAGG`）将多行数据连接成一个字符串。 - **字符串转换成多列** - 使用 `SUBSTR` 和 `INSTR` 函数，或者 `REGEXP_SUBSTR` 函数将字符串拆分成多列。 - **字符串转换成多行** - 同样地，可以通过 `REGEXP_SUBSTR` 或其他字符串解析函数来实现。 #### 五、总结 本文档提供了Oracle中行列转换的六个常见案例及其具体实现方法。这些转换技巧对于提高数据库查询效率、简化数据处理流程等方面具有重要意义。通过上述示例的学习，读者可以更好地理解如何利用Oracle的内置功能来满足不同场景下的数据转换需求。