The magical function of Sys_connect_by_path in Oracle

In Oracle, the primary function of the Sys_connect_by_path function is to differentiate all child nodes under a parent node by a character, and then connect to display in a single column.

AD:

The Sys_connect_by_path function in Oracle is a very important function, and here is an example of using the Sys_connect_by_path function, as follows:

      
  
 
              
   
  

  
CREATE TABLE Test (a varchar2 (ten), B varchar2 (10));
              
   
  

  
              
   
  

  
INSERT into TEST (A, B) VALUES (' 1 ', ' I ');
              
   
  

  
INSERT into TEST (A, B) VALUES (' 1 ', ' they ');
              
   
  

  
INSERT into TEST (A, B) VALUES (' 2 ', ' one ');
              
   
  

  
INSERT into TEST (A, B) VALUES (' 2 ', ' up ');
              
   
  

  
COMMIT;
              
   
  

  
              
   
  

  
SELECT A, B from TEST
              
   
  

  
              
   
  

  
A B
              
   
  

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

  
1 I
              
   
  

  
1 of them
              
   
  

  
21
              
   
  

  
From 2
              
   
  

  
              
   
  

  
Now it is necessary to achieve the following effect,
              
   
  

  
A B
              
   
  

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

  
1 me, guys.
              
   
  

  
21, up
              
   
  

  
         
  
 

Just want to use one sentence of SQL to return the result.

      
 
   
  
              
  
    
   
              
  
    
   
              
  
    
   
              
  
    
   
START with RN = 1
              
  
    
   
CONNECT by RN- 1 = PRIOR
              
  
    
   
and A = PRIOR
              
  
    
   
         
 
   
  

The main function of the Sys_connect_by_path function is that all child nodes under a parent node can be distinguished by a character, and then the connection is displayed in a column.

The purpose of the Row_number function is very extensive, and the function is to generate a sequence number for each row of records that are queried. The method of producing the serial number is controlled by the statement inside the over () function.

The magical function of Sys_connect_by_path in Oracle