[font=Tahoma,][size=26px]Oracle的列转行函数：LISTAGG()[font=Tahoma,][font=Helvetica, Tahoma, Arial, sans-serif]Oracle的列转行函数：LISTAGG()

[font=Helvetica, Tahoma, Arial, sans-serif]先看示例代码：[font=Monaco,]Sql代码 [color=#108ac6]http://dacoolbaby.iteye.com/blog/1698957#tc_qz_original=460811474][img]http://ugc.qpic.cn/adapt/0/2ee5400c-4bd8-db84-2414-218f1c40f02a/0[/img]

[list=1]
[*]with temp as(
[*] select 'China' nation ,'Guangzhou' city from dual union all
[*] select 'China' nation ,'Shanghai' city from dual union all
[*] select 'China' nation ,'Beijing' city from dual union all
[*] select 'USA' nation ,'New York' city from dual union all
[*] select 'USA' nation ,'Bostom' city from dual union all
[*] select 'Japan' nation ,'Tokyo' city from dual
[*])
[*]select nation,listagg(city,',') within GROUP (order by city)
[*]from temp
[*]group by nation

[font=Helvetica, Tahoma, Arial, sans-serif] 这是最基础的用法：[font=Helvetica, Tahoma, Arial, sans-serif]LISTAGG(XXX,XXX) WITHIN GROUP( ORDER BY XXX)
[font=Helvetica, Tahoma, Arial, sans-serif]用法就像聚合函数一样，通过Group by语句，把每个Group的一个字段，拼接起来。[font=Helvetica, Tahoma, Arial, sans-serif]非常方便。
[font=Helvetica, Tahoma, Arial, sans-serif]同样是聚合函数，还有一个高级用法：[font=Helvetica, Tahoma, Arial, sans-serif]就是over(partition by XXX)[font=Helvetica, Tahoma, Arial, sans-serif]也就是说，在你不实用Group by语句时候，也可以使用LISTAGG函数：[font=Monaco,]Sql代码 [color=#108ac6]http://dacoolbaby.iteye.com/blog/1698957#tc_qz_original=460811474][img]http://ugc.qpic.cn/adapt/0/2ee5400c-4bd8-db84-2414-218f1c40f02a/0[/img]

[list=1]
[*]with temp as(
[*] select 500 population, 'China' nation ,'Guangzhou' city from dual union all
[*] select 1500 population, 'China' nation ,'Shanghai' city from dual union all
[*] select 500 population, 'China' nation ,'Beijing' city from dual union all
[*] select 1000 population, 'USA' nation ,'New York' city from dual union all
[*] select 500 population, 'USA' nation ,'Bostom' city from dual union all
[*] select 500 population, 'Japan' nation ,'Tokyo' city from dual
[*])
[*]select population,
[*] nation,
[*] city,
[*] listagg(city,',') within GROUP (order by city) over (partition by nation) rank
[*]from temp

[font=Helvetica, Tahoma, Arial, sans-serif]总结：LISTAGG()把它当作SUM()函数来使用就可以了。[font=Helvetica, Tahoma, Arial, sans-serif]Oracle11.2新增了LISTAGG函数，可以用于字符串聚集，测试如下：

1,版本
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

2，测试数据
SQL>
SQL> select empno,ename,deptno from scott.emp;

EMPNO ENAME DEPTNO
----- ---------- ------
7369 SMITH 20
7499 ALLEN 30
7521 WARD 30
7566 JONES 20
7654 MARTIN 30
7698 BLAKE 30
7782 CLARK 10
7788 SCOTT 20
7839 KING 10
7844 TURNER 30
7876 ADAMS 20
7900 JAMES 30
7902 FORD 20
7934 MILLER 10

14 rows selected

3，作为聚集函数
SQL> SELECT deptno,
2 LISTAGG(ename, ',') WITHIN GROUP(ORDER BY ename) AS employees
3 FROM scott.emp
4 GROUP BY deptno;

DEPTNO EMPLOYEES
------ --------------------------------------------------------------------------------
10 CLARK,KING,MILLER
20 ADAMS,FORD,JONES,SCOTT,SMITH
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

SQL>
--更换排序列
SQL> SELECT deptno,
2 LISTAGG(ename, ',') WITHIN GROUP(ORDER BY hiredate) AS employees
3 FROM scott.emp
4 GROUP BY deptno;

DEPTNO EMPLOYEES
------ --------------------------------------------------------------------------------
10 CLARK,KING,MILLER
20 SMITH,JONES,FORD,SCOTT,ADAMS
30 ALLEN,WARD,BLAKE,TURNER,MARTIN,JAMES
--order by必须存在
SQL> SELECT deptno,
2 LISTAGG(ename, ',') WITHIN GROUP() AS employees
3 FROM scott.emp
4 GROUP BY deptno;

SELECT deptno,
LISTAGG(ename, ',') WITHIN GROUP() AS employees
FROM scott.emp
GROUP BY deptno

ORA-30491: ORDER BY 子句缺失

SQL> SELECT deptno,
2 LISTAGG(ename, ',') WITHIN GROUP(order by null) AS employees
3 FROM scott.emp
4 GROUP BY deptno;

DEPTNO EMPLOYEES
------ --------------------------------------------------------------------------------
10 CLARK,KING,MILLER
20 ADAMS,FORD,JONES,SCOTT,SMITH
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
==〉按字母顺序排列
4，LISTAGG作为分析函数使用
SQL> SELECT empno,
2 ename,
3 deptno,
4 LISTAGG(ename, ',') WITHIN GROUP(ORDER BY ename) over(partition by deptno) AS employees
5 FROM scott.emp;

EMPNO ENAME DEPTNO EMPLOYEES
----- ---------- ------ --------------------------------------------------------------------------------
7782 CLARK 10 CLARK,KING,MILLER
7839 KING 10 CLARK,KING,MILLER
7934 MILLER 10 CLARK,KING,MILLER
7876 ADAMS 20 ADAMS,FORD,JONES,SCOTT,SMITH
7902 FORD 20 ADAMS,FORD,JONES,SCOTT,SMITH
7566 JONES 20 ADAMS,FORD,JONES,SCOTT,SMITH
7788 SCOTT 20 ADAMS,FORD,JONES,SCOTT,SMITH
7369 SMITH 20 ADAMS,FORD,JONES,SCOTT,SMITH
7499 ALLEN 30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
7698 BLAKE 30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
7900 JAMES 30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
7654 MARTIN 30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
7844 TURNER 30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
7521 WARD 30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

14 rows selected
5,其他实现方法参考
--model
SQL> SELECT deptno, vals
2 FROM (SELECT deptno, RTRIM(vals, ',') AS vals, rn
3 FROM scott.emp MODEL PARTITION BY(deptno) DIMENSION BY(ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY ename) AS rn) MEASURES(CAST(ename AS VARCHAR2(4000)) AS vals) RULES(vals [ ANY ] ORDER BY rn DESC = vals [ CV() ] || ',' || vals [ CV() + 1 ]))
4 WHERE rn = 1
5 ORDER BY deptno;

DEPTNO VALS
---------- --------------------------------------------------------------------------------
10 CLARK,KING,MILLER
20 ADAMS,FORD,JONES,SCOTT,SMITH
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

--表函数：WMSYS.WM_CONCAT，10G已经提供该函数
SQL>
SQL> SELECT deptno, WMSYS.WM_CONCAT(ename) AS vals --<-- WM_CONCAT ~= STRAGG 2 FROM scott.emp 3 GROUP BY deptno; DEPTNO VALS ------ -------------------------------------------------------------------------------- 10 CLARK,MILLER,KING 20 SMITH,FORD,ADAMS,SCOTT,JONES 30 ALLEN,JAMES,TURNER,BLAKE,MARTIN,WARD