Oracle Advanced SQL (4) -- SQL column and column conversion topic 2, oracle -- SQL

Source: Internet
Author: User

Oracle Advanced SQL (4) -- SQL column and column conversion topic 2, oracle -- SQL

[Previous topic] column-and-column conversion 1-Introduction to functions of aggregate and unaggregate

Implementation case 1001: lufei1001 shanzhi1001 namei1001 qiaoba: 1001 lufei, shanzhi, namei, qiaoba typical row-column conversion: there are many ways to achieve this. Here, the listparts function is provided after oracle11g. It is best to achieve the above effect. Let's take a look at the syntax structure of the listparts syntax listparts function:> listparts ([,]) within group (order by) [over (partition by)] Although listparts is an aggregate function, it can provide analysis functions (such as an optional OVER () clause ). In listparts, the following elements are required: the order by clause in the column or expression that needs to be aggregated with group keyword grouping. The following example demonstrates the listparts function. This article describes the listparts function added in oracle 11g release 2, listparts is an oracle built-in function that implements string aggregation first.
-- This select no, listparts (EMP, ',') within GROUP (order by null) AS valsFROM onepieceGROUP by no; -- wmsys. wm_concat Method -- before 11, you can use this select no, WMSYS. WM_CONCAT (emp) AS valsFROM onepieceGROUP by no; -- use the set function; Use the to_string function to convert the set to a string. Select no, TO_STRING (CAST (COLLECT (emp) AS varchar2_ntt) AS valsFROM onepieceGROUP by no;

The above shows that there is an over function in the listparts function syntax. Of course, our listparts can also be used as an analysis function.

SELECT NO,    listagg(EMP, ',') within GROUP (ORDER BY NULL) over (partition by no)FROM   onepiece

There are to_string functions in the 3rd implementation methods. The source code is as follows:

CREATE OR REPLACE FUNCTION to_string( nt_in        IN varchar2_ntt, delimiter_in IN VARCHAR2 DEFAULT ',') RETURN VARCHAR2 IS v_idx PLS_INTEGER; v_str VARCHAR2(32767); v_dlm VARCHAR2(10);BEGIN v_idx := nt_in.FIRST; WHILE v_idx IS NOT NULL LOOP  v_str := v_str || v_dlm || nt_in(v_idx);  v_dlm := delimiter_in;  v_idx := nt_in.NEXT(v_idx); END LOOP; RETURN v_str;END to_string;

Finally: what is the opposite of the above effect? There is another knowledge point that is not explained; it is not provided for the time being.

 

Author: li0924

Time:

The copyright of this article belongs to the author. You are welcome to repost this article, but you must keep this statement without the author's consent and provide the original article connection clearly on the article page.

Related Article

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.