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]