Using Connect By to Build Data Tree in Oracle

This example uses the connect by select clause to return data in a tree structure.

Our example has one table, which links to itself, parentId = treeTestId of the parent record.

CREATE TABLE treeTest
( treeTestId NUMBER(3,0),
parentId NUMBER(3,0),
description VARCHAR2(20) );

Now some test data

INSERT INTO treeTest VALUES ( 1, NULL, 'Animal');
INSERT INTO treeTest VALUES ( 2, NULL, 'Vegtable');
INSERT INTO treeTest VALUES ( 3, NULL, 'Mineral');
INSERT INTO treeTest VALUES ( 4, 1, 'Elephant');
INSERT INTO treeTest VALUES ( 5, 4, 'African');
INSERT INTO treeTest VALUES ( 6, 4, 'Indian');
INSERT INTO treeTest VALUES ( 7, 1, 'Giraffe');
INSERT INTO treeTest VALUES ( 8, 1, 'Rhino');
INSERT INTO treeTest VALUES ( 9, 8, 'Black');
INSERT INTO treeTest VALUES ( 10, 8, 'White');
INSERT INTO treeTest VALUES ( 11, 2, 'Potato');
INSERT INTO treeTest VALUES ( 12, 11, 'New');
INSERT INTO treeTest VALUES ( 13, 11, 'King Edward');
INSERT INTO treeTest VALUES ( 14, 2, 'Carrott');
INSERT INTO treeTest VALUES ( 15, 3, 'Gold');
INSERT INTO treeTest VALUES ( 16, 3, 'Silver');
INSERT INTO treeTest VALUES ( 17, 3, 'Lead');

And a select statement to return the data. The level pseudo column tells us which branch level the data belongs to.

SELECT treeTestId,
description,
parentId,
level
FROM treeTest
CONNECT BY PRIOR treeTestId = parentId
START WITH parentId IS NULL;

And another select statement, with the data tabbed in depending on its level.

SELECT LPAD(' ',(level-1)*2)||description
FROM treeTest
CONNECT BY PRIOR treeTestId = parentId
START WITH parentId IS NULL;

Done.