Oracle数据库行列转换新玩法

Oracle数据库行列转换新玩法

引用

CSDN

等

12

来源

1.

https://blog.csdn.net/real_girl/article/details/132907437

2.

https://blog.csdn.net/weixin_44688973/article/details/120524523

3.

https://blog.csdn.net/cclovezbf/article/details/130685278

4.

https://blog.csdn.net/weixin_42634260/article/details/123738474

5.

https://blog.csdn.net/qq_18913129/article/details/124419667

6.

https://blog.csdn.net/qq_36360841/article/details/83316813

7.

https://blog.csdn.net/tttzzzqqq2018/article/details/132841908

8.

https://developer.baidu.com/article/details/2857464

9.

https://blog.csdn.net/weixin_42414098/article/details/110347789

10.

https://www.cnblogs.com/jokingremarks/p/17382442.html

11.

https://www.cnblogs.com/rogerfederer/p/17579614.html

12.

http://www.821121.com/?p=1380

在Oracle数据库中，行列转换是常见的数据处理需求。随着Oracle版本的演进，提供了多种实现方式，包括传统的UNION ALL、MODEL、COLLECTION方法，以及11g版本引入的PIVOT和UNPIVOT函数。本文将详细介绍这些方法的使用场景和具体示例，帮助读者根据实际情况选择最适合的解决方案。

UNION ALL

UNION ALL是最基础的列转行方法，通过多次查询并合并结果实现。适用于列数较少的情况。

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;

特点：简单直观，但需要手动处理每一列，适合列数较少的情况。

UNPIVOT（推荐）

UNPIVOT是Oracle 11g引入的新特性，提供了更简洁的语法和更好的性能。

SELECT *
FROM student1
UNPIVOT (score FOR subject IN ("语", "数", "英"));

特点：语法简洁，支持动态处理多列，是11g+版本的首选方案。

MODEL子句

MODEL子句在Oracle 10g中引入，通过模型子句实现列转行，灵活性较高。

SELECT id, cn, cv FROM t_col_row
MODEL PARTITION BY (id) DIMENSION BY (0 AS n)
MEASURES ('xx' AS cn, 'yyy' AS cv, c1, c2, c3)
RULES (cn='c1', cv=c1, ...);

特点：灵活性高，适合复杂逻辑，但语法相对复杂。

集合（COLLECTION）

通过自定义对象和集合类型实现列转行，适用于固定列场景。

SELECT id, t.cn, t.cv
FROM t_col_row, TABLE(cv_varr(cv_pair('c1',c1), cv_pair('c2',c2), ...)) t;

特点：需要预先定义对象和集合类型，适合固定列场景。

聚合函数 + CASE/MAX(DECODE)

适用于低版本Oracle，通过聚合函数结合条件判断生成多列。

SELECT id,
  MAX(CASE WHEN cn='c1' THEN cv END) AS c1,
  MAX(CASE WHEN cn='c2' THEN cv END) AS c2
FROM t_row_col GROUP BY id;

特点：兼容性好，但代码可读性较差。

PIVOT（推荐）

PIVOT函数在Oracle 11g中引入，提供了更简洁的行转列语法。

SELECT *
FROM scores
PIVOT (MAX(score) FOR subject IN ('Chinese', 'Math', 'English'));

特点：支持多列动态转换，语法清晰，性能高效，是11g+版本的首选方案。

LISTAGG

Oracle 11.2引入的LISTAGG函数，支持排序和分组，结果长度限制4000字节。

SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY sal) AS names
FROM emp GROUP BY deptno;

特点：支持排序和分组，但结果长度有限制。

XMLAGG（超长字符串处理）

适用于处理超长字符串，支持CLOB类型。

SELECT GRADE, XMLAGG(XMLPARSE(CONTENT CLASS||';') ORDER BY CLASS).GETCLOBVAL()
FROM CLASS_STATISTICS GROUP BY GRADE;

特点：支持CLOB类型，适合超长字符串拼接。

WM_CONCAT（已弃用）

Oracle 12c+已弃用，建议改用LISTAGG。

SELECT id, WM_CONCAT(name) FROM test GROUP BY id;

注意：Oracle 12c+已弃用，建议改用LISTAGG。

当列名或数量不确定时，需通过PL/SQL动态生成SQL语句，结合EXECUTE IMMEDIATE执行。

• 列转行：优先使用UNPIVOT（11g+），低版本可选UNION ALL或MODEL。
• 行转列：优先使用PIVOT（11g+），低版本可用MAX(DECODE)。
• 字符串聚合：使用LISTAGG或XMLAGG，避免已弃用的WM_CONCAT。
• 动态列：需结合动态SQL实现灵活性。

通过以上方法，可以灵活应对Oracle数据库中的各种行列转换需求。选择合适的方法不仅能简化代码，还能提升查询性能。