Oracle数据库行列转换全攻略

"行列转换总结" 行列转换是数据库管理和数据分析中常见的操作，特别是在处理表格数据时。这个总结主要针对Oracle数据库中的几种行列转换方法，适用于8i、9i、10g及以后的版本。 1. 列转行 列转行通常是为了将一列的数据展开成多行显示。在Oracle中，有两种主要的方法实现这一转换： 2.1 使用`UNION ALL` 这是最基础的列转行方法，通过将每一列的数据作为新的行进行`UNION ALL`操作。例如，对于表`t_col_row`，我们可以创建一个新的结果集，其中包含每一条记录的ID以及每列的名称和值。如果不想包含空值行，可以通过添加`WHERE COLUMN IS NOT NULL`来过滤。 2.2 使用`MODEL`子句 Oracle 10g引入了`MODEL`子句，提供了更强大的数据转换能力。在这个例子中，`MODEL`可以根据每个ID分区，然后将每一列的非空值转换为一行，cn表示列名，cv表示对应的值。 3. 行转列 行转列是将多行数据合并到同一列中，常用于报表展示。Oracle提供了多种方法实现行转列，如`PIVOT`操作： 3.1 `PIVOT`操作 Oracle 9i引入了`PIVOT`，允许用户将行转换为列。例如，如果我们想根据ID将上面的列c1、c2、c3转换为列，可以使用如下语句： ```sql SELECT * FROM t_col_row PIVOT ( MAX(value) FOR column_name IN ('c1', 'c2', 'c3') ); ``` 这将为每种列名生成一个新列，并将对应ID的值放入相应的新列中。 4. 多列转换成字符串 有时需要将多列数据合并成一个字符串，可以使用`LISTAGG`函数（Oracle 11g及以上）或者`WM_CONCAT`（Oracle 10g）实现。例如： ```sql SELECT id, LISTAGG(c1 || ',' || c2 || ',' || c3, ',') WITHIN GROUP (ORDER BY id) AS combined_values FROM t_col_row GROUP BY id; ``` 5. 多行转换成字符串 当需要将多行数据合并成一个单一的字符串时，可以结合`LISTAGG`或`WM_CONCAT`与`GROUP BY`语句一起使用。 6. 字符串转换成多列 将字符串拆分为多列通常涉及到字符串解析。Oracle提供了`SUBSTR`、`INSTR`、`REGEXP_SUBSTR`等函数来实现。例如，如果一个字符串以逗号分隔，可以使用`REGEXP_SUBSTR`配合正则表达式来拆分。 7. 字符串转换成多行 如果字符串中每个分隔符代表一行，可以使用`REGEXP_REPLACE`配合`WITHIN GROUP (ORDER BY)`来转换。例如，如果字符串中的每个单词用空格分隔，可以这样转换： ```sql WITH data AS (SELECT REGEXP_REPLACE('word1 word2 word3', '(\S+)', '\1||', 1, 1, NULL, 1) str FROM DUAL) SELECT REGEXP_SUBSTR(str, '.*?([^||]+)$', 1, level, NULL, 1) AS word FROM data CONNECT BY REGEXP_SUBSTR(str, '.*?([^||]+)$', 1, level, NULL, 1) IS NOT NULL; ``` 以上就是关于行列转换的基本总结，涵盖了常见的转换场景。在实际应用中，根据数据结构和需求，可能还需要结合其他数据库功能进行更复杂的数据转换。