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

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.