Oracle数据库行列转换完全指南

"Oracle 行转列 列转行 总结" Oracle数据库中，行列转换是数据处理中的常见需求，尤其在数据分析和报表展示时。本文主要总结了六种Oracle中的行列转换方法，适用于8i、9i、10g及以后的版本。 1. 列转行 列转行是将表格中的多列数据转换为单列，每个原列的值作为新行的数据。有两种常见的实现方式： - UNION ALL 可以通过UNION ALL操作符将多个列合并到一个新列中。例如，对于表t_col_row，可以将c1、c2、c3列转换为一行，每列对应一个新行。如果不需要包含空值，可以通过WHERE子句过滤掉。 ```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; ``` - MODEL子句 Oracle 10g引入的MODEL子句提供更灵活的转换能力，可以处理更复杂的转换逻辑。例如，可以使用以下语句实现列转行： ```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 AS cv, 'c1' AS cn) RULES ( cv[FOR rn FROM 1 TO 3 WHEN (rn = 1 OR cn[rn] IS NOT NULL) ] = c1, cv[FOR rn FROM 1 TO 3 WHEN (rn = 2 OR cn[rn] IS NOT NULL) ] = c2, cv[FOR rn FROM 1 TO 3 WHEN (rn = 3 OR cn[rn] IS NOT NULL) ] = c3, cn[FOR rn FROM 1 TO 3] = CASE WHEN rn = 1 THEN 'c1' WHEN rn = 2 THEN 'c2' WHEN rn = 3 THEN 'c3' END ); ``` 2. 行转列 行转列则是将单列中的多行数据转换为多列。这通常需要使用PIVOT操作，它在Oracle 11g及更高版本中可用。 ```sql -- 假设我们有一个结果集，需要将c1列的值作为新列名 SELECT * FROM ( SELECT id, c1 FROM t_col_row ) PIVOT ( MAX(c1_value) -- 原列的值 FOR c1 IN ('v11' AS v11, 'v21' AS v21, 'v31' AS v31) ); ``` 3. 多列转换成字符串 可以使用LISTAGG函数将多列合并为一个字符串。这个函数在Oracle 11g中引入。 ```sql SELECT id, LISTAGG(c1, ',') WITHIN GROUP (ORDER BY c1) AS col1_str, LISTAGG(c2, ',') WITHIN GROUP (ORDER BY c2) AS col2_str FROM t_col_row GROUP BY id; ``` 4. 多行转换成字符串 类似地，可以使用XMLAGG函数将多行数据合并为一个字符串，通常用于创建CSV格式的数据。 ```sql SELECT id, RTRIM(XMLAGG(XMLELEMENT(e, c1 || ',')).EXTRACT('//text()'), ',') AS col1_str FROM t_col_row GROUP BY id; ``` 5. 字符串转换成多列 当字符串中包含分隔符时，可以使用SUBSTR、INSTR等函数配合WITH子句解析字符串，将其转换为多列。 ```sql WITH data AS ( SELECT id, REGEXP_SUBSTR(c1, '[^,]+', 1, level) AS value FROM t_col_row CONNECT BY PRIOR id = id AND PRIOR SYS_GUID() IS NOT NULL AND level <= LENGTH(REGEXP_REPLACE(c1, '[^,]+')) + 1 ) SELECT id, MAX(CASE WHEN level = 1 THEN value END) AS col1, MAX(CASE WHEN level = 2 THEN value END) AS col2, MAX(CASE WHEN level = 3 THEN value END) AS col3 FROM data GROUP BY id; ``` 6. 字符串转换成多行 可以使用REGEXP_SUBSTR函数和层次查询（CONNECT BY）将字符串拆分成多行。 ```sql SELECT id, REGEXP_SUBSTR(c1, '[^,]+', 1, level) AS value FROM t_col_row CONNECT BY PRIOR id = id AND PRIOR SYS_GUID() IS NOT NULL AND level <= LENGTH(REGEXP_REPLACE(c1, '[^,]+')) + 1; ``` 这些示例展示了Oracle中处理行列转换的基本方法。在实际应用中，根据具体需求，可能需要结合其他SQL函数或特性进行调整。在进行转换时，应考虑数据量、性能和查询复杂性等因素。