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

Source: Internet
Author: User

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)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.