Use of Oracle row and column conversion functions

Racle 10g Wmsys.wm_concat Use of the row and column conversion functions:

First let's take a look at this magical function wm_concat (column name), which separates the column values by the "," number, and displays them as a line, and then the example below to see how this magical function can be applied
1. Divide the results by commas and print them in one line. (If you need to change something else, you can use the Replace function: replace (WM_CONCAT (name), ', ', ' | ') ) Select t.u_id, Wmsys.wm_concat (t.goods), Wmsys.wm_concat (T.goods | | ' (' | | | t.u_id | | ' kg ') from Tb_index T Group by t.u_id;
2, Over (partition by t.u_id) Usage: Select t.u_id, www.2cto.com wmsys.wm_concat (T.goods | | ' (' | | | t.u_id | | ' kg ') over (partition by t.u_id) from Tb_index t;
3. Over (order by t.u_id) usage: Select t.u_id, Wmsys.wm_concat (T.goods | | ' (' | | | t.u_id | | ' kg ') over (partition by t.u_id) from Tb_index t;
4. Lazy extension usage: (Big table Many fields I need to string up) Select ' Select ' | | Wm_concat (' t. ' | | column_name) | | ' from Tb_index t ' from User_tab_columns where table_name= ' Tb_index '; Sys_connect_by_path (ColumnName, seperator): Used to construct a tree path, so it needs to be used with connect by. The main function of the Sys_connect_by_path function is that all child nodes under a parent node can be distinguished by a character, and then the connection is displayed in a column

Select T.areaid, T.parentareaid, T.areaname, Sys_connect_by_path (T.areaname, '-') area from Tb_index t   Start with t.areaname = ' China ' connect by T.parentareaid = Prior t.areaid; Www.2cto.com
LISTAGG:11GR2 also added an analytic function Listagg, which functions to implement the connection of the string CREATE table T (ID number, name varchar2 (), type VARCHAR2); insert Into T-select RowNum, object_name, object_type from Dba_objects;select listagg (name, ', ') within group (order by ID) from T where RowNum < 10;select type, Listagg (name, ', ') within group (order by ID) name from twhere type in (' DIRECTORY ', ' JAVA SOURCE ', ' SCHEDULE ') group by Type;select name, Listagg (name, ', ') within group (order by ID) over (partition by T ype) S_name from T where type in (' DIRECTORY ', ' JAVA SOURCE ', ' SCHEDULE ');

Use of Oracle row and column conversion functions