Oracle行列转换函数

VM_CONCAT

vm_concat可以用来进行行转列，默认以逗号分隔，可通过vm_concat(name,’,’,’|’)修改为|

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13


SQL> select * from test
id    name
---- -----
1     a
2     b
1     c
2     d
        
SQL> select id,wm_concat(name) from test group by id
id    name
---- ------
1     a,c
2     b,d

Oracle 12C之后该函数已经不再支持了

LISTAGG

LISTAGG是Oracle 11g中推出的函数，也可以用来做行转列

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13


SQL> select distinct DEPTNO,listagg(ENAME,';') WITHIN GROUP (order by ename) over (PARTITION BY DEPTNO) from SCOTT.EMP;
DEPTNO	LISTAGG(ENAME,';')WITHINGROUP(ORDERBYENAME)
------- ---------------------------------------------
  10	      CLARK;KING;MILLER
  20	      ADAMS;FORD;JONES;SCOTT;SMITH
  30	      ALLEN;BLAKE;JAMES;MARTIN;TURNER;WARD

SQL> select distinct DEPTNO,listagg(ENAME,';') WITHIN GROUP (order by ename)  from SCOTT.EMP group by DEPTNO;
DEPTNO	LISTAGG(ENAME,';')WITHINGROUP(ORDERBYENAME)
------- ---------------------------------------------
  10	      CLARK;KING;MILLER
  20	      ADAMS;FORD;JONES;SCOTT;SMITH
  30	      ALLEN;BLAKE;JAMES;MARTIN;TURNER;WARD

19C才开始支持distinct去重

XMLAGG

XMLAGG是一个聚合函数，可以将数据聚集成XML格式数据。XMLAGGf返回的类型为clob，最大字节长度为32767，因此当行转列字符长度超出限制时可以采用改方式来避免该错误。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13


SQL> select DEPTNO,XMLAGG(XMLELEMENT(CONTENT,ename||',') ORDER BY ename).EXTRACT('//text()').getclobval() as ename from scott.emp group by DEPTNO;
DEPTNO	ENAME
------- -------------------------------------
  10    CLARK,KING,MILLER,
  20    ADAMS,FORD,JONES,SCOTT,SMITH,
  30    ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD,

SQL> select DEPTNO,xmlagg(xmlparse(content ENAME ||',' wellformed) order by ENAME).getclobval()  as ename from SCOTT.EMP GROUP BY DEPTNO;
DEPTNO	ENAME
------- -------------------------------------
  10    CLARK,KING,MILLER,
  20    ADAMS,FORD,JONES,SCOTT,SMITH,
  30    ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD,

PIVOT与UNPIVOT

Oracle 11g中又加入了两个函数：PIVOT和UNPIVOT，用于进行行专列和列转行，与SQL Server中同名函数功能一致

基础数据

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16


SELECT job,deptno,SUM(sal) AS sum_sal
    FROM   emp
    GROUP  BY job,deptno
    ORDER  BY job,deptno;
  
JOB           DEPTNO    SUM_SAL
--------- ---------- ----------
ANALYST         20	   6000
CLERK           10	   1300
CLERK           20	   1900
CLERK           30	   950
MANAGER         10	   2450
MANAGER         20	   2975
MANAGER         30	   2850
PRESIDENT       10	   5000
SALESMAN        30	   5600

行转列

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13


select *from (select sal,deptno,job from scott.emp)
  pivot (
    sum(sal)
    for deptno in(10,20,30,40)
  );

JOB          10     20   30   40
----------  ------ ---- ---- ----
ANALYST       -    6000   -    - 
CLERK        1300  1900  950   - 
SALESMAN      -     -    5600  - 
MANAGER      2450  2975  2850  - 
PRESIDENT    5000   -     -    -

UNPIVOT和PIVOT相反，用于进行列转行

更多详细信息请参考Pivot Operator