Oracle row-to-column (row to column 1, row to column)

The new Listagg function in the Oracle 11g Release 2 release, Listagg is an Oracle built-in function that implements string aggregation;
Listagg (column, ' delimiter ') within group (Order by column)-(Partition by column)
The delimiter can be empty,
Order BY mandatory option, can ORDER by null

(1)Select Status, Listagg (risk_id, ', ') within group (order by risk_id) from Rp_risk Group by status;
Group BY status, Risk_id all merged in one row
(2) similar to many aggregation functions, Listagg can be analyzed by adding the over () clause
Select risk_id, Status, Listagg (risk_id, ', ') within group (order by risk_id) through (partition by status) from Rp_risk;
Selects all risk_id in the same department as the current risk_id and merges the strings
(3)The result column size of the Listagg aggregation is limited to the maximum value of the VARCHAR2 type (for example, 4000);
(4) merging strings can also be used with Wm_concat (column_name), and all versions of Oracle can use this function
Listagg () is Oracle 11g Release 2 only;
(5) Reference links

http://xpchild.blog.163.com/blog/static/10180985920108485721969/

--Listagg (the value of merging multiple rows is a string and is displayed in only one column)SelectStatusCount(*), Listagg (risk_id,',') withinGroup(Order  byRISK_ID) fromRp_riskGroup  bystatus; Selectrisk_id, status, Listagg (risk_id,',') withinGroup(Order  byRISK_ID) Over(Partition byStatus fromRp_risk; Selectrisk.risk_id, Listagg (officer.last_name|| ',' ||Officer.first_name,'; ') withinGroup(Order  by NULL) fromrp_risk Risk, Rp_risk_area_ref Re, rp_risk_area area, Rp_risk_officer officerwhererisk.risk_id=re.risk_id andre.risk_area_id=AREA.RISK_AREA_ID (+)   andarea.risk_officer_id=OFFICER.RISK_OFFICER_ID (+)  Group  byrisk.risk_id; --Pivot (line train, change the value of multiple rows to multi-column display) (the value of a column, that is, the value of a column as the column of the new one, and the value below the column can only come from a column)Select *  from     (Selectrisk.risk_id, Re.risk_area_order, Officer.last_name|| ',' ||Officer.first_name FullName fromrp_risk Risk, Rp_risk_area_ref Re, rp_risk_area area, Rp_risk_officer officerwhererisk.risk_id=re.risk_id andre.risk_area_id=AREA.RISK_AREA_ID (+)     andarea.risk_officer_id=OFFICER.RISK_OFFICER_ID (+)Order  byrisk.risk_iddesc, Re.risk_area_order) pivot (Max(FullName) forRisk_area_orderinch(1Primaryofficer,2AddtionalOffcier1,3ADDTIONALOFFCIER2))Order  byrisk_iddesc; --Decode (line train, change the value of multiple rows to multi-column display) (The column of Decode, which is the value of a certain columns, that is, the value of a column as a new column Column,max aggregation function can also be replaced with Sum, MIN, avg and other aggregation functions)Selectrisk_id,--Max (Decode (Risk_area_order, 1, fullname)) Primaryofficer,--Max (Decode (Risk_area_order, 2, fullname)) AddtionalOffcier1,--Max (Decode (Risk_area_order, 3, fullname)) AddtionalOffcier1min(Decode (Risk_area_order,1, FullName)) Primaryofficer,min(Decode (Risk_area_order,2, FullName)) AddtionalOffcier1,min(Decode (Risk_area_order,3, FullName)) AddtionalOffcier1 from     (Selectrisk.risk_id, Re.risk_area_order, Officer.last_name|| ',' ||Officer.first_name FullName fromrp_risk Risk, Rp_risk_area_ref Re, rp_risk_area area, Rp_risk_officer officerwhererisk.risk_id=re.risk_id andre.risk_area_id=AREA.RISK_AREA_ID (+)     andarea.risk_officer_id=OFFICER.RISK_OFFICER_ID (+)Order  byrisk.risk_id, Re.risk_area_order)Group  byrisk_idOrder  byrisk_id;

Reference Links:

More comprehensive:

Http://blog.sina.com.cn/s/blog_010630c30100fdyp.html

http://blog.csdn.net/kingston001/article/details/7949629

About Pivot:

Http://www.oracle.com/technetwork/cn/articles/11g-pivot-101924-zhs.html

About Wm_concat:

http://blog.csdn.net/jwlsky/article/details/7619819

Http://www.cnblogs.com/8765h/archive/2012/01/14/2374378.html

Oracle row-to-column (row to column 1, row to column)