Oracle row-to-column analysis functions

Oracle row to column first look at the source data:

Method One: Wm_concat group By this method is not a problem .

SELECT code_ts,             1 ' : ' | | ELEMENT) ELEMENT        where code_ts='020745'  Group by Code_ts;

The results obtained:

You may find that the serial numbers are not sorted in order. If there is no demand, then it is possible. See method Two if you want to sort.

Method Two: Wm_concat over with special data will be error. "Operation value exceeds the system limit". cause I don't know, who knows trouble tell me about it.

Select  from (     SELECT code_ts,            1':' | | ELEMENT) Over (order by S_num) ELEMENT,             To_number (Dense_rank ()-Over                       (PARTITION by Code_ts ORDER by S_num DESC)) orderno< c12/>where code_ts='020745'  ORDER by Code_ts,s_num                 where t.orderno=1;

Wm_concat over

If the result is not error:

Method Three: Sys_connect_by_path over this method is not a problem . But note : Sys_connect_by_path The connection symbol here do not use commas , Oracle will error, if necessary to use, you can replace with replace; The connector cannot be present in your first parameter sequence . You can use multiple combinations of connector characters.

It's not easy to exist in your column, I use it here, plus a space. Of course you can also use a combination connector that does not exist, and then replace the unwanted part of the connector with the

SelectCode_ts,element from (        Selectcode_ts,element, Row_number () over (partition by code_ts ORDER BY Code_ts,rank Desc) Ename_path_rank from (        SelectCode_ts,rank, LTrim (Sys_connect_by_path (rank||':'|| ELEMENT,'; '),'; ') ELEMENT from                 (                   SelectCode_ts,element,row_number () over (partition by code_ts ORDER BY s_num desc) rank fromT_merch_elementwherecode_ts='020745'ORDER BY s_num desc) Connect by Code_ts= Prior code_ts and rank-1=prior rank)) whereename_path_rank=1;

Sys_connect_by_path

Effects such as:

Oracle row-to-column analysis functions