Oracle Advanced SQL (iv)--SQL column conversion Topic 2

"Previous topic" column conversion 1--Introduction of Pivot and Unpivot function

Implementation case1001 Lufei1001 Shanzhi1001 Namei1001 QiaobaThe implementation results are as follows:1001 Lufei,shanzhi,namei,qiaobaTypical row and column conversions: There are many ways to achieve this. The Listagg function is provided after oracle11g, which is best used to achieve the above effect; let's see Listagg syntax.The syntax structure of the LISTAGG function is as follows:>listagg ([,]) within group (order by) [Over (partition by)]Although Listagg is an aggregation function, it can provide analysis functionality (such as an optional over () clause). with Listagg, the elements in the following are required: columns or expressions that need to be aggregated With GROUP keywordsORDER BY clause in a groupThe following example demonstrates the use of the Listagg function This article describes the new Listagg function in the Oracle 11g Release 2 release, Listagg is an Oracle built-in function that implements string aggregation to implement the case code first

--listagg method  11g is recommended for use with this select NO,    listagg (EMP, ', ') within GROUP (ORDER by NULL) as Valsfrom   onepiecegroup< C7/>by No;--wmsys.wm_concat method--11 can be used before this select NO,    wmsys. Wm_concat (EMP) as Valsfrom   onepiecegroup  by no;--uses the aggregate function, and converts the collection to a string using the To_string function. Requires more than 10g select NO,    to_string (CAST (COLLECT (EMP) as Varchar2_ntt) as Valsfrom   Onepiecegroup  

Above we see the Listagg function syntax has over function, of course, our Listagg can also do analysis functions

SELECT NO,    listagg (EMP, ', ') within GROUP (ORDER by NULL) through (partition by NO) from   onepiece

The 3rd method of implementation has the To_string function, where the source code is as follows:

CREATE OR REPLACE FUNCTION to_string (nt_in in        Varchar2_ntt, delimiter_in in VARCHAR2 DEFAULT ', ') RETURN to VARCHAR2 is V_idx Pls_integer; V_str VARCHAR2 (32767); V_DLM VARCHAR2 (10); BEGIN v_idx: = nt_in. First; While v_idx are 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 effect of the above? Because there is a knowledge point is not explained, temporarily not provided.

Author: li0924

Date: 2015-01-12

This article is copyrighted by the author, welcome reprint, but without the consent of the author must retain this paragraph, and in the article page obvious location to the original link.

Oracle Advanced SQL (iv)--SQL column conversion Topic 2