Where the table is not normalized and the output is required in a tree structure, the ‘union’ operator can do a lot for you. Here in the example, table ‘tree’ contains three fields viz. main-group, sub-group, and element. And details below are everything else required.
 
SQL> select * from tree;
 
MGRP       SGRP       ELE
---------- ---------- ----------
MAINGRP1   SUBGRP11   ELEMENT111
MAINGRP1   SUBGRP11   ELEMENT112
MAINGRP1   SUBGRP12   ELEMENT121
MAINGRP1   SUBGRP12   ELEMENT122
MAINGRP2   SUBGRP21   ELEMENT211
MAINGRP2   SUBGRP21   ELEMENT212
MAINGRP2   SUBGRP22   ELEMENT221
MAINGRP2   SUBGRP22   ELEMENT222
MAINGRP3   SUBGRP31   ELEMENT311
MAINGRP3   SUBGRP31   ELEMENT312
MAINGRP3   SUBGRP32   ELEMENT321
MAINGRP3   SUBGRP32   ELEMENT322
 
12 rows selected.
 
SQL> select datatree from(
  2  select distinct ' |-- ' ||  mgrp datatree, mgrp ord from tree
  3  union all
  4  select distinct ' |    |-- ' ||  sgrp datatree, mgrp||sgrp ord from tree
  5  union all
  6  select distinct ' |    |    |-- ' ||  ele datatree, mgrp||sgrp||ele ord from tree
  7  ) order by ord
  8  /
 
DATATREE
-------------------------
 |-- MAINGRP1
 |    |-- SUBGRP11
 |    |    |-- ELEMENT111
 |    |    |-- ELEMENT112
 |    |-- SUBGRP12
 |    |    |-- ELEMENT121
 |    |    |-- ELEMENT122
 |-- MAINGRP2
 |    |-- SUBGRP21
 |    |    |-- ELEMENT211
 |    |    |-- ELEMENT212
 |    |-- SUBGRP22
 |    |    |-- ELEMENT221
 |    |    |-- ELEMENT222
 |-- MAINGRP3
 |    |-- SUBGRP31
 |    |    |-- ELEMENT311
 |    |    |-- ELEMENT312
 |    |-- SUBGRP32
 |    |    |-- ELEMENT321
 |    |    |-- ELEMENT322
 
21 rows selected.
 
SQL> 
 
»»»»»»»   by Santosh Kumar ? Original @ http://santu4you.spaces.live.com