Purpose
SYS_CONNECT_BY_PATH() returns the paths of column values from the root to nodes. The column values of each row returned by the CONNECT BY condition are separated with the specified separator.
Note
This function is valid only in hierarchical queries.
Syntax
SYS_CONNECT_BY_PATH(column,'char')
Parameters
Parameter |
Description |
|---|---|
| column | The column whose values need to be returned. It can be of the CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type. |
| char | The separator. It can be of the CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type. |
Return type
The return type is VARCHAR2.
Examples
Create a table named tbl1 and insert test data into it. The ABC value of the name column is the root value, and other values are node values. Query the paths from the root value ABC to all other values of the name column.
obclient> CREATE TABLE tbl1(z_id INT,name VARCHAR2(20),n_id INT);
Query OK, 0 rows affected
obclient> INSERT INTO tbl1 VALUES(0,'ABC',''),
-> (1,'An',0),(2,'Bn',0),(3,'Cn',0),
-> (4,'A1',1),(5,'B1',2),(6,'C1',3),
-> (7,'C2',6),(8,'A2',4),(9,'B2',5),
-> (10,'A3',8),(11,'A4',10),(12,'B3',9);
Query OK, 13 rows affected
Records: 13 Duplicates: 0 Warnings: 0
obclient> COMMIT;
Query OK, 0 rows affected
obclient> SELECT * FROM tbl1;
+------+------+------+
| Z_ID | NAME | N_ID |
+------+------+------+
| 0 | ABC | NULL |
| 1 | An | 0 |
| 2 | Bn | 0 |
| 3 | Cn | 0 |
| 4 | A1 | 1 |
| 5 | B1 | 2 |
| 6 | C1 | 3 |
| 7 | C2 | 6 |
| 8 | A2 | 4 |
| 9 | B2 | 5 |
| 10 | A3 | 8 |
| 11 | A4 | 10 |
| 12 | B3 | 9 |
+------+------+------+
13 rows in set
obclient> SELECT z_id,name,n_id,SYS_CONNECT_BY_PATH(name, '/') "Path"
FROM tbl1
START WITH n_id IS NULL
CONNECT BY PRIOR n_id = z_id
ORDER BY z_id;
+------+------+------+---------------------+
| Z_ID | NAME | N_ID | Path |
+------+------+------+---------------------+
| 0 | ABC | NULL | /ABC |
| 1 | An | 0 | /ABC/An |
| 2 | Bn | 0 | /ABC/Bn |
| 3 | Cn | 0 | /ABC/Cn |
| 4 | A1 | 1 | /ABC/An/A1 |
| 5 | B1 | 2 | /ABC/Bn/B1 |
| 6 | C1 | 3 | /ABC/Cn/C1 |
| 7 | C2 | 6 | /ABC/Cn/C1/C2 |
| 8 | A2 | 4 | /ABC/An/A1/A2 |
| 9 | B2 | 5 | /ABC/Bn/B1/B2 |
| 10 | A3 | 8 | /ABC/An/A1/A2/A3 |
| 11 | A4 | 10 | /ABC/An/A1/A2/A3/A4 |
| 12 | B3 | 9 | /ABC/Bn/B1/B2/B3 |
+------+------+------+---------------------+
13 rows in set