Application of Oracle sys_connect_by_path

I personally think this function is actually very useful. sys_connect_by_path (field name, The Connection Symbol between two fields). Note that the Connection Symbol here should not be a comma. Oracle will report an error. If you must use it, replace can be used as follows: Replace (field name, original character ,',').
Also, you must create a tree before using this function. Otherwise, it is useless.
For example:
Objective: To write a project with the same num value as seq1, seq2, seq3 ,...... Format
(Select num, replace (max (sql0 ),';',',')
From (select num, sys_connect_by_path (sql1, ';') as sql0
From (select num, sql1, RN, lead (RN) over (partition by num order by RN) rn1
From (select num, sql1, row_number () over (order by num, sql1 DESC) Rn from tlsbk ))
Start with num = '1' and rn1 is null
Connect by rn1 = prior RN ));
Result:
Num Replace (max (sql0 ),';',',')
--------------------------------------------------------
1 sql0, sql1, sql2
2 sql20, sql21, sql23, sql24, sql25
3 sql30, sql31, sql32, sql33, sql34, sql35, sql36

Tree Structure and Its dedicated function sys_connect_by_path)
Simple Tree Structure
Common applications of trees
After learning this function, I used the orgindustries table for a test:
Normal Tree Structure
Select lpad ('', 6 * (level-1) | industry, indlevel, indid, pindid
From orgindustries
Start with indid = 1
Connect by pindid = prior indid
The result is as follows:
Indlevel indid pindid
Apparel & apparel 1 1 0
Clothing 2 2 1
Women's 3 3 2

Inverted tree
The following example shows a "reciprocal"-A inverted tree structure.
Select lpad ('', 6 * (level-1) | industry, indlevel, indid, pindid
From orgindustries
Start with indid = 20
Connect by indid = prior pindid;
This is the standard result:
Indlevel indid pindid
Second-hand clothing 3 20 2
Clothing 2 2 1
Apparel & apparel 1 1 0
Conclusion
Whether the positive tree or the inverted tree, the key lies in the connect by condition.
Positive tree: It must be 'parent '= prior 'sub'
Inverted tree: The value must be 'sub' = prior 'parent'

Conditional filtering of tree structures
If we want to cut down a branch on the tree and discard the structure behind the branch, can this be implemented? Of course. However, if the where condition is not used, only a single condition can be removed.
Therefore, the tree-like filtering condition needs to be added to connect.

The test is as follows: because the real environment is close to the actual environment, use the sys_connect_by_path function to display the environment in advance.

Environment without any conditions:
Select areaname, sys_connect_by_path (areaname ,',')
From areas bb
Start with areaname = 'mainland China'
Connect by parentareaid = prior areaid

Result:
1 mainland China, Mainland China
2 Beijing, Mainland China, Beijing
3 Beijing, Mainland China, Beijing, and Beijing
4 Dongcheng District, Mainland China, Beijing, Dongcheng District
5 Xicheng district, Mainland China, Beijing, Xicheng District
22. Guangdong, Mainland China, and Guangdong
23 Guangzhou, Mainland China, Guangdong, and Guangzhou
24 Shanwei, Mainland China, Guangdong, Shanwei
25. Chaoyang, Mainland China, Guangdong, and Chaoyang
46 Shanghai, Mainland China, and Shanghai
47 Shanghai, Mainland China, Shanghai, and Shanghai
48 Huangpu District, Mainland China, Shanghai, Huangpu District
49 Zhabei District, Mainland China, Shanghai, Zhabei District

SQL statements with where filter conditions:
Select areaname, sys_connect_by_path (areaname ,',')
From areas bb
Where BB. areaid> 861000
Start with areaname = 'mainland China'
Connect by parentareaid = prior areaid

Result:
2 Beijing, Mainland China, Beijing
3 Beijing, Mainland China, Beijing, and Beijing
4 Dongcheng District, Mainland China, Beijing, Dongcheng District
5 Xicheng district, Mainland China, Beijing, Xicheng District
22. Guangdong, Mainland China, and Guangdong
23 Guangzhou, Mainland China, Guangdong, and Guangzhou
24 Shanwei, Mainland China, Guangdong, Shanwei
25. Chaoyang, Mainland China, Guangdong, and Chaoyang
46 Shanghai, Mainland China, and Shanghai
47 Shanghai, Mainland China, Shanghai, and Shanghai
48 Huangpu District, Mainland China, Shanghai, Huangpu District
49 Zhabei District, Mainland China, Shanghai, Zhabei District

Conclusion: The "1 mainland China, Mainland China" data is removed.

Filter conditions added with connect:
Select areaname, sys_connect_by_path (areaname ,',')
From areas bb
Where BB. areaid> 861000
Start with areaname = 'mainland China'
Connect by parentareaid = prior areaid and areaname <> 'guangdong'

Result:
2 Beijing, Mainland China, Beijing
3 Beijing, Mainland China, Beijing, and Beijing
4 Dongcheng District, Mainland China, Beijing, Dongcheng District
5 Xicheng district, Mainland China, Beijing, Xicheng District
46 Shanghai, Mainland China, and Shanghai
47 Shanghai, Mainland China, Shanghai, and Shanghai
48 Huangpu District, Mainland China, Shanghai, Huangpu District
49 Zhabei District, Mainland China, Shanghai, Zhabei District

Conclusion: After the entire Guangdong branch is removed, only Beijing and Shanghai are returned.

Sys_connect_by_path Function
The function sys_connect_by_path is used as follows:

Select industry, sys_connect_by_path (industry ,'/')
From orgindustries
Start with indid = 3
Connect by indid = prior pindid;

Result:
Women's/women's clothing
Clothing/women's clothes/clothing
Clothing and apparel/Women's Wear/clothing and apparel

In this way, we can achieve single-row splicing of the result set in the tree structure:

We only need to obtain the largest field and then OK.

The test is as follows:

Select max (sys_connect_by_path (industry ,'/'))
From orgindustries
Start with indid = 3
Connect by indid = prior pindid;

Result:
/Women's Wear/clothing and apparel

Complex tree structure-changing multiple columns to a single column
Tree structures are also divided into single-tree and multi-tree structures (My name actually refers to single-tree and multi-tree structures)
In the following case, the tree we must construct belongs to the single tree.
Original environment
The environment is as follows:
Select * from test;

Result:
1 N1
1 N2
1 N3
1 N4
1 N5
3 T1
3 T2
3 T3
3 T4
3 T5
3 T6
2 M1

Tree Creation
The script is as follows:
Select No, Q,
No + row_number () over (order by no) Rn,
Row_number () over (partition by no order by no) rn1
From Test

The result is as follows:
No Q rn rn1
1 N1 2 1
1 N2 3 2
1 N3 4 3
1 N4 5 4
1 N5 6 5
2 M1 8 1
3 T1 10 1
3 T2 11 2
3 T3 12 3
3 T4 13 4
3 T5 14 5
3 T6 15 6

The purpose of each column is:
The main purpose of the rn1 column is to group data. Based on the value '1', we can start with to use it.

The RN column is mainly used for connect. In fact, it is the tree we want.
First Branch: 2, 3, 4, 5, 6
Second Branch: 8
Third Branch :,

Why do we need to break it down? 7, 9 aims to differentiate each branch. After reading the specific SQL statements, you will understand the statements here.

Killer
Now that we have a tree, we can use the tree function sys_connect_by_path and connect by to splice the required multi-column values.

The script is as follows:
Select No, sys_connect_by_path (Q ,',')
From (
Select No, Q,
No + row_number () over (order by no) Rn,
Row_number () over (partition by no order by no) rn1
From Test
)
Start with rn1 = 1
Connect by rn-1 = prior Rn

Result:
1, N1
1, N1, N2
1, N1, N2, N3
1, N1, N2, N3, N4
1, N1, N2, N3, N4, N5
2, M1
3, T1
3, T1, T2
3, T1, T2, T3
3, T1, T2, T3, T4
3, T1, T2, T3, T4, T5
3, T1, T2, T3, T4, T5, T6

Weapon
The final value we want is a single column value. In fact, think about it, that is, the longest line. So it's easy. We directly group by, and then take the max value.
The script is as follows:
Select No, max (sys_connect_by_path (Q ,','))
From (
Select No, Q,
No + row_number () over (order by no) Rn,
Row_number () over (partition by no order by no) rn1
From Test
)
Start with rn1 = 1
Connect by rn-1 = prior Rn
Group by no

Result:
1, N1, N2, N3, N4, N5
2, M1
3, T1, T2, T3, T4, T5, T6

If you think the preceding ',' is not easy to understand, you can use ltrim to remove it. Or use substr.
As follows:
Ltrim (max (sys_connect_by_path (Q ,',')),',')
Or
Substr (max (sys_connect_by_path (Q, ','), 2)