Oracle peer grouping

Use the sys_connect_by_path (column,

Use the sys_connect_by_path (column,

Oracle peer grouping
Use the sys_connect_by_path (column, '') function as an example ^.

Table Structure:

Create table test (

Bookid char (3) not null,

Author varchar2 (10) not null

);

Insert into test values ('001', 'jack ');

Insert into test values ('001', 'Tom ');

Insert into test values ('002 ', 'wang ');

Insert into test values ('002 ', 'zhang ');

Insert into test values ('002 ', 'lil ');

Commit;

Select * from test;

The result is as follows:

BOO AUTHOR

-----------------

001 jack

001 tom

002 wang

002 zhang

002 li

The expected result is:

BOO AUTHOR

-----------------------------

001 jack & tom

002 wang & zhang & li

The SQL statement is as follows:

Select bookid, substr (max (sys_connect_by_path (author, '&'), 3) author

From

(Select bookid, author, id, lag (id) over (partition by bookid order by id) pid

-- (The last column or is) lead (id) over (partition by bookid order by id desc) pid

From (select bookid, author, rownum id from test ))

Start with pid is null

Connect by prior id = pid

Group by bookid;

Explanation:

Sys_connect_by_path (column, '') // column is the column name, and the characters to be added are in the middle ''.

This function is not used to concatenate result sets (merge rows), but to construct tree paths. Therefore, it must be used with connect.

Test is only a common table. How can it be changed to a tree structure? We need to add a pid and id.

We only need to add a rownum for the id.

Select bookid, author, rownum id from test;

BOO AUTHOR ID

----------------------------

001 jack 1

001 tom 2

002 wang 3

002 zhang 4

002 li 5

The previous pid record is not the parent node of the next record. Here we need the function lag () to retrieve the previous record, which is opposite to lead.

// Change the lag (id) over (order by id) pid to lead (id) over (order by id desc) pid.

Select bookid, author, id, lag (id) over (order by id) pid

From (select bookid, author, rownum id from test );

BOO AUTHOR ID PID

-------------------------------------------

001 jack 1

001 tom 2 1

002 wang 3 2

002 zhang 4 3

002 li 5 4

Because we need to divide our pid by bookid, we need to add partition by in the analysis function over. We can see what is different from the following results.

Select bookid, author, id, lag (id) over (partition by bookid order by id) pid

From (select bookid, author, rownum id from test );

BOO AUTHOR ID PID

-------------------------------------------

001 jack 1

001 tom 2 1

002 wang 3

002 zhang 4 3

002 li 5 4