0%

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