Without Oracle, you'd need to read all the rows, rearrange them in memory and then output them. But that's not trivial.

First, I'd select all the rows in an way that meant you never saw a child before it's parent:

SELECT category_id, category_name, main_category
  FROM test
  ORDER BY main_category, category_id
[download]

Then I'd store the results in a hash keyed on category_id where each element was a reference to an array containing the category_name, and an array of child category_id's (initially empty):

  $category{$category_id} = [ $category_name, [] ];
[download]

and (as long as the main_category_id was not 0?) I'd add the category_id onto the end of the list of the parent's children:

push @{$category{$main_category}->[1]}, $category_id;
[download]

which would give a hash like this:

%category = (
  1 => [ 'One',      [5, 6, 7] ],
  2 => [ 'Two',      [8, 9] ],
  3 => [ 'Three',    [] ],
  4 => [ 'Four',     [] ],
  5 => [ 'Six',      [10, 13] ],
  6 => [ 'Seven',    [] ],
  7 => [ 'Eight',    [] ],
  ...
);
[download]

Then you have a structure you can recurse down through (but you need to know where to start - I guess that means when you fetch the query results you need to keep track of the top level categories):

  sub print_categories {
    my $level = shift;
    foreach my $id (@_) {
      print "$level: $category{$id}->[0]\n";
      print_categories($level + 1, @{$category{$id}->[0]});
    }
  }

  print_categories(0, @top_level_categories);
[download]

Of course that will look pretty much like gibberish if you don't have a solid grasp of Perl references - I did say it wasn't trivial. Also, the code snippets are not tested so there are likely bugs there (in addition to the obvious holes like actually executing the query and fetching the results).

Update: On second thoughts, that SELECT probably doesn't guarantee to give you the parents before the children. I can't think about it anymore, it's late and my head hurts :-) Good night!

Update 2: OK, I've slept on it, the SELECT definitely doesn't guarantee to give you the parents before the children (although it might happen to have that effect for the data set you quoted). Rather than trying to put the name and the children in one hash, it would be easier to use one hash for each. When processing an item, the category_id would need to be appended to the parent's list of children, you'd just need to create the list first if it didn't already exist.

OK, I've slept on it, the SELECT definitely doesn't guarantee to give you the parents before the children

So, that doesn't matter if you do the autoviv correct:

my $sth = $dbh->prepare("SELECT category_id, category_name, main_categ
+ory FROM test");
$sth->execute();
$sth->bind_columns(\my($category_id, $category_name, $main_category));
my @cats;
while ($sth->fetch) {
  $cats[$category_id][0] = $category_name;
  push @{$cats[$main_category][1]}, $category_id;
}
[download]

Autoviv is cool, when used properly.

-- Randal L. Schwartz, Perl hacker
Be sure to read my standard disclaimer if this is a reply.

If you're using Oracle, there is a proprietary extension to SQL using the keywords connect by which returns the kind of hierarchical result set you're looking for. I'm not aware of any other database that offer this (although I'd love to know).

Update: I don't have an Oracle DB handy but I think the query would look something like this:

SELECT LEVEL, category_name
   FROM test
   CONNECT BY PRIOR category_id = main_category;
[download]

SELECT a.category_id, a.category
  FROM test a, test b
 WHERE b.main_category = a.category_id
[download]

Warning: This is untested and may require some tweaking to make work in the exact way you require.

my $tree = build_tree(0);
print_tree($tree);
.
.
sub build_tree {
    my $index = shift();
    my %tree;
    foreach ("select id from table where cat_id = $index") {
       %tree{$_} = build_tree($_) || $_;
   }
   \%tree;
}
.
etc.
[download]

%title_hash = ();
%index_hash = ();

$dbh = <your db connection handle>;
$sql = qq[select category_id, category_name, main_category from test];
$sth = $dbh->prepare($sql);
$sth->execute;
while ( ( $child, $name, $parent ) = $sth->fetchrow ) {
  # pad the category id and main category numbers with leading zeros
  # (so we can use the numbers as keys of a hash and sort on the keys)
  $child = &pad($child);
  $parent = &pad($parent);
  # save the category name for this category id
  $title_hash{$child} = $name;
  # add the category id to an array in the hash
  # for the main category
  push(@{$index_hash{$parent}},$child); # hash of arrays
}
$sth->finish;
$dbh->disconnect;

# so now we should have the parent/child
# relationships of all the categories
# in our %index_hash hash...
#
# @{$index_hash{<main_category number>}} is
# an array of the child categories
# associated with each main_category

# set the initial recursion level
$level = 0;
# what is the top-most main category?
$topkey = (sort keys %index_hash)[0];
# start the recursive loop
&show_children($topkey);


##############

sub show_children {

  # go down one level
  $level++;
  # set some 'my' variables
  my $parent_key = $_[0];
  my $child_key = 0;
  my $dash = '';
  # what are the child categories of this parent?
  my @children = sort @{$index_hash{$parent_key}};
  # are there any children of this parent?
  if ( 0 < scalar(@children) ) {
    # yes there are, so let's display each of them
    foreach $child_key (@children) {
      # if level 1, dash is '--' ...
      # if level 2, dash is '----' ... etc
      $dash = '-' x $level;
      $dash .= $dash;
      print qq[$dash $title_hash{$child_key}\n];
      # treat this child as a parent and see if
      # it has any children to display
      &show_children($child_key);
    }
  }
  # come back up one level
  $level--;

}

##############

sub pad {

  # turns '4' into '0004'
  # ...good for when you want to use numbers
  # as hash keys so you can sort the keys
  # in numerical order
  # ... otherwise perl sorts 1 through 10 as
  # 1, 10, 2, 3, 4, 5, 6, 7, 8, 9
  # (this is because hash keys are always seen as
  # strings by the perl interpreter)
  my $v = $_[0];
  return substr( ('0000'.$v), -4, 4 );

}
[download]

Thanks for your help. I solved this problem using other method. But your coding is excellent. Anyway thanks a lot ... It was an excellent work. But the problem while displaying is not correct. In first level, it starts with (-- ) But i want first level without (--), next level(with 2 dash),next child contain (4 dash). Could you please point out the portion to modify the code. Thanks.