Oracle usage, function memo history, oracle usage function memo

Oracle usage, function memo history, oracle usage function memo
Listparts

Select * from emp

Select listparts (ename, '-') withingroup (orderby deptno desc) from emp;

You can see that the function is similar to wm_concat. You can customize the connector. The difference is as follows:

Listparts: A function provided by 11g2. It does not support distinct. The concatenation length cannot exceed 4000. the return value of the function is of the varchar2 type and the maximum length is 4000.

Compared with wm_concat, listparts can be sorted. For example
Select deptno, listparts (ename, ';') withingroup (orderby ename) enames from emp groupby deptno;

With table

SQL Code

1 2 3 4 5 6 7 8 9 10 11 12 13

With temp ( Select 500 population, 'China' nation, 'guangzhou 'city from dual union all Select 1500 population, 'China' nation, 'shanghai' city from dual union all Select 500 population, 'China' nation, 'beijing' city from dual union all Select 1000 population, 'usa' nation, 'New York 'city from dual union all Select 500 population, 'usa' nation, 'bostom' city from dual union all Select 500 population, 'Japan 'Nation, 'Tokyo 'city from dual ) Select population, Nation, City, Listparts (city, ',') within GROUP (order by city) over (partition by nation) rank From temp

With table as is similar to creating a temporary table. It can be queried only once and then destroyed. Multiple temporary tables can be created at the same time, for example:

With sql1

(Select to_char (a) s_name from test_tempa ),

Sql2

(Select to_char (B) s_name

From test_tempb

Wherenotexists (select s_name from sql1 whererownum = 1 ))

Select *

From sql1

Unionall

Select *

From sql2

Pivot unregister

For more information about column and column conversion, see

Summary of SQL statements for Oracle row-to-column and column-to-row Conversion