oracle 的行列转换函数,Oracle行列转换函数

VM_CONCAT

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

2

3

4

5

6

7

8

9

10

11

12

13SQL> 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

13SQL> select distinct DEPTNO,listagg(ENAME,';') WITHIN GROUP (order by ename) over (PARTITION BY DEPTNO) from SCOTT.EMP;

DEPTNOLISTAGG(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;

DEPTNOLISTAGG(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

13SQL> select DEPTNO,XMLAGG(XMLELEMENT(CONTENT,ename||',') ORDER BY ename).EXTRACT('//text()').getclobval() as ename from scott.emp group by DEPTNO;

DEPTNOENAME

------- -------------------------------------

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;

DEPTNOENAME

------- -------------------------------------

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

16SELECT 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

13select *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相反，用于进行列转行