How to Use the SYS_CONNECT_BY_PATH function in Oracle

In Oracle, The SYS_CONNECT_BY_PATH function is a very important function. The following describes an example of using the SYS_CONNECT_BY_PATH function. The example is as follows:

 
 

  
  
Create table test (a varchar2 (10), B varchar2 (10 ));
  
  
 
  
  
Insert into test (A, B) VALUES ('1', 'I ');
  
  
Insert into test (A, B) VALUES ('1 ');
  
  
Insert into test (A, B) VALUES ('2', '1 ');
  
  
Insert into test (A, B) VALUES ('2', 'start ');
  
  
COMMIT;
  
  
 
  
  
Select a, B FROM TEST
  
  
 
  
  
A B
  
  
--------------------
  
  
1. Me
  
  
1.
  
  
2 I
  
  
From 2
  
  
 
  
  
Now we need to achieve the following results,
  
  
A B
  
  
--------------------
  
  
1. Me
  
  
2, starting from
  
  
 
 
 

Only one SQL statement is used to return the result.

 
 

  
  
SELECT A, LTRIM(MAX(SYS_CONNECT_BY_PATH(B, ',')), ',') B  
  
  
FROM (SELECT B, A, ROW_NUMBER() OVER(PARTITION BY A ORDER BY B DESC) RN  
  
  
          FROM TEST)  
  
  
START WITH RN = 1 
  
  
CONNECT BY RN - 1 = PRIOR RN  
  
  
       AND A = PRIOR A  
  
  
GROUP BY A; 
 
 

The SYS_CONNECT_BY_PATH function is used to distinguish all the child nodes under a parent node by a specific character, and then connect them to a column for display.

The row_number function is widely used to generate a sequence number for each row of records queried. The production sequence number method is controlled by the statements in the over () function.
 

Use of Oracle to_char Functions

Implementation of oracle function return table

Learn about the Oracle FBI Index

Multiple table Connection Methods in Oracle

Example of using SQL recursive statements in oracle