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

?

?