Purpose
This function returns the path from the root to the node, with column values separated by a specified delimiter for each row returned by the CONNECT BY condition.
Note
This function is valid only in hierarchical queries.
Syntax
SYS_CONNECT_BY_PATH(column,'char')
Parameters
| Parameter | Description |
|---|---|
| column | The name of the column from which to retrieve data. It can be of the CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type. |
| char | The delimiter. It can be of the CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type. |
Return type
VARCHAR2
Examples
Create a tbl1 table and insert test data, where the ABC value in the name column is the root value, and other values are nodes. Query all paths from the name column to ABC.
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