Oracle11.2新特性之listagg函数 (行列转换)
作为聚集函数
SELECT deptno,LISTAGG(ename, ',') WITHIN GROUP(ORDER BY ename) AS employees
FROM scott.emp
GROUP BY deptno;
多表
select jzg.tid,
jzg.firstname,
listagg(d.dwmc, ',') within group(order by d.dwmc) aa
from xtgl_jzg jzg, xtgl_yhdwgxb gx, xtgl_depart d
where jzg.tid = gx.yhbh(+)
and gx.dwh = d.dwdm(+)
GROUP BY jzg.tid, jzg.firstname
多表,及表中存在blob类型字段,自关联
select x.*, j.aa
from (select jzg.tid,
jzg.firstname,
listagg(d.dwmc, ',') within group(order by d.dwmc) aa
from xtgl_jzg jzg, xtgl_yhdwgxb gx, xtgl_depart d
where jzg.tid = gx.yhbh(+)
and gx.dwh = d.dwdm(+)
GROUP BY jzg.tid, jzg.firstname) j,
xtgl_jzg x
where j.tid = x.tid
order by x.tid
--摘自 移动IM接口的用户管理