Oracle教程 2023-06-12

Oracle 不同列值的合并处理（oracle不同列值合并）

Oracle 不同列值的合并处理

Oracle数据库中，常常有需要将相同值的行合并成一行的情况。但是，有时候需要将不同列的值合并到同一行，以便更好地进行分析和比较。在这种情况下，Oracle提供了一些函数和技巧，可以帮助我们轻松地实现这个任务。

1. LISTAGG函数

LISTAGG函数是Oracle的一个字符串聚合函数，用于将一列中的值合并为一个字符串，以逗号或其他分隔符分隔。该函数的语法如下：

LISTAGG(列名, 分隔符) WITHIN GROUP (ORDER BY 排序列)

例如，以下查询将合并DEPT表的DEPTNO列和DNAME列：

“`sql

SELECT LISTAGG(DEPTNO||’-‘||DNAME, ‘, ‘) WITHIN GROUP (ORDER BY DEPTNO)

FROM DEPT;


结果如下：

10-ACCOUNTING, 20-RESEARCH, 30-SALES, 40-OPERATIONS


注意，由于LISTAGG函数将多个值合并为一个字符串，因此结果可能会超出VARCHAR2的最大长度限制。为避免这种情况，可以使用MAX_STRING_SIZE参数将VARCHAR2类型的长度限制从默认的4000增加到32767。具体方法是：

ALTER SYSTEM SET MAX_STRING_SIZE = EXTENDED;


2. XMLAGG函数

XMLAGG函数将一列中的值合并为一个XML字符串，类似于LISTAGG函数，但是可以避免字符串长度过长的问题。例如：

```sql
SELECT XMLAGG(XMLELEMENT(E, EMPNO||','||ENAME||',')) AS EMP_LIST
FROM EMP;

结果如下：

7369,SMITH,7499,ALLEN,7521,WARD,...

另外，可以使用XMLTABLE函数将XML字符串转换为表格形式：

“`sql

SELECT emp.* FROM

xmltable(‘/EMP_LIST/E’

passing xmltype(‘7369,SMITH,7499,ALLEN,…’)

columns empno number path ‘./text()[1]’,

ename varchar2(20) path ‘./text()[2]’) emp;


结果如下：

EMPNO ENAME

—— —–

7369 SMITH

7499 ALLEN

7521 WARD

7566 JONES

7654 MARTIN

7698 BLAKE

7782 CLARK

…


3. PIVOT操作

PIVOT操作可以将行数据转换为列数据，有助于更直接地进行汇总和比较。例如，以下查询将EMP表按JOB分组，然后将SAL列的值在低、中、高三个范围内分别计算，并将结果显示在不同的列中：

```sql
SELECT * FROM (
  SELECT JOB, SAL, CASE WHEN SAL
  FROM EMP
) PIVOT (COUNT(*) FOR SAL_RANGE IN ('低' AS LOW, '中' AS MEDIUM, '高' AS HIGH))
ORDER BY JOB;

结果如下：

JOB          LOW    MEDIUM    HIGH
-----------  -----  -------  -----
ANALYST              2           
CLERK                4        1  
MANAGER              1                 
PRESIDENT                            1
SALESMAN     4

可以看到，原本以行的方式呈现的数据，现在在不同的列中呈现。这样，我们可以更方便地进行分类汇总和比较。注意，PIVOT操作是Oracle 11g及以上版本才支持的。

综上所述，Oracle提供了多种函数和技巧，可以方便地实现不同列值的合并处理，从而更好地进行数据分析和比较。不同的场景可以选择不同的方法，根据实际需求进行选择和组合。