Oracle row and column conversion functions

Use of the Oracle 10g wmsys. wm_concat row and column conversion function:
First, let's take a look at this magic function wm_concat (column name). This function can separate the column values with "," and display them as a row. Next, let's take an example, let's see how this magic function is applied.

1. Use commas to separate the results by group and print the results in one row. (If you want to change to another one, 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 | 'jin )')
From tb_index t
Group by t. u_id;

2. over (partition by t. u_id) usage:
Select t. u_id,
Wmsys. wm_concat (t. goods | '(' | t. u_id | 'jin) ') 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 | 'jin) ') over (partition by t. u_id)
From tb_index t;

4. Lazy extension usage: (I Need To concatenate many fields in a large table)
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 the tree path. Therefore, it must be used together with connect.
The main function of sys_connect_by_path is to distinguish all child nodes under a parent node by a specific character, and then connect them to a column for display.

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;

 

 

Listparts: 11gr2 also adds an analysis function listparts, which implements string connection.

Create table t (id number, name varchar2 (30), type varchar2 (20 ));

Insert into t
Select rownum, object_name, object_type from dba_objects;

Select listparts (name, ',') within group (order by id)
From t
Where rownum <10;

Select type, listparts (name, ',') within group (order by id) name
From t
Where type in ('Directory ', 'java source', 'schedule ')
Group by type;

Select name,
Listparts (name, ',') within group (order by id) over (partition by type) s_name
From t
Where type in ('Directory ', 'java source', 'schedule ');

For more information about Oracle, see Oracle topics page http://www.bkjia.com/topicnews.aspx? Tid = 12