ORACLE行转列(行转1列,行转多列)
在oracle 11g release 2 版本中新增的listagg函数,listagg是一个实现字符串聚合的oracle内建函数;
listagg(column,'分隔符') within group (order by column) over(partition by column)
分隔符可以为空,
order by必选项,可以order by null
(1)select status, listagg(risk_id, ',') within group (order by risk_id) from rp_risk group by status;
以status分组,将risk_id全部合并显示在一行
(2)与许多的聚合函数类似,listagg通过加上over()子句可以实现分析功能
select risk_id, status, listagg(risk_id, ',') within group (order by risk_id) over(partition by status) from rp_risk;
选出与当前risk_id在同一个部门的所有risk_id并合并字符串
(3)listagg聚合的结果列大小限制在varchar2类型的最大值内(比如4000);
(4)合并字符串也可以用wm_concat(column_name),所有版本的oracle都可以用这个函数
listagg()是oracle 11g release 2才有;
(5)参考链接
http://xpchild.blog.163.com/blog/static/10180985920108485721969/
--listagg(合并多行的值为字符串,只用一列来显示) select status, count(*), listagg(risk_id, ',') within group (order by risk_id) from rp_risk group by status; select risk_id, status, listagg(risk_id, ',') within group (order by risk_id) over(partition by status) from rp_risk; select risk.risk_id, listagg(officer.last_name || ',' || officer.first_name, '; ') within group(order by null) from rp_risk risk, rp_risk_area_ref re, rp_risk_area area, rp_risk_officer officer where risk.risk_id = re.risk_id and re.risk_area_id = area.risk_area_id(+) and area.risk_officer_id = officer.risk_officer_id(+) group by risk.risk_id; --pivot(行专列,将多行的值改为多列显示)(for in的那个column,是某个列的值,也就是将某个列的值作为新的列的column,这个column下边的值好像只能来自一列) select * from (select risk.risk_id, re.risk_area_order, officer.last_name || ',' || officer.first_name fullname from rp_risk risk, rp_risk_area_ref re, rp_risk_area area, rp_risk_officer officer where risk.risk_id = re.risk_id and re.risk_area_id = area.risk_area_id(+) and area.risk_officer_id = officer.risk_officer_id(+) order by risk.risk_id desc, re.risk_area_order) pivot(max(fullname) for risk_area_order in (1 primaryOfficer, 2 addtionalOffcier1, 3 addtionalOffcier2)) order by risk_id desc; --decode(行专列,将多行的值改为多列显示)(decode的那个column,是某个列的值,也就是将某个列的值作为新的列的column,MAX聚集函数也可以用sum、min、avg等其他聚集函数替代) select risk_id, --max(decode(risk_area_order, 1, fullname)) primaryOfficer, --max(decode(risk_area_order, 2, fullname)) addtionalOffcier1, --max(decode(risk_area_order, 3, fullname)) addtionalOffcier1 min(decode(risk_area_order, 1, fullname)) primaryOfficer, min(decode(risk_area_order, 2, fullname)) addtionalOffcier1, min(decode(risk_area_order, 3, fullname)) addtionalOffcier1 from (select risk.risk_id, re.risk_area_order, officer.last_name || ',' || officer.first_name fullname from rp_risk risk, rp_risk_area_ref re, rp_risk_area area, rp_risk_officer officer where risk.risk_id = re.risk_id and re.risk_area_id = area.risk_area_id(+) and area.risk_officer_id = officer.risk_officer_id(+) order by risk.risk_id, re.risk_area_order) group by risk_id order by risk_id;
参考链接:
比较全面的:
http://blog.sina.com.cn/s/blog_010630c30100fdyp.html
http://blog.csdn.net/kingston001/article/details/7949629
关于pivot的:
http://www.oracle.com/technetwork/cn/articles/11g-pivot-101924-zhs.html
关于wm_concat的:
http://blog.csdn.net/jwlsky/article/details/7619819
http://www.cnblogs.com/8765h/archive/2012/01/14/2374378.html