Oracle query to fetch data in the form of a Tree

Question

I have a table with some data as follows

 Id | Title  | Category
 --  -----     --------
  1   Beans    Food
  2   Pizza    Food
  3   SQL      Book
  4   Avatar   Movie
  5   Oracle   Book

I would like to organize this kind of table data in the form of a TREE with root node as Category.

Food
  Beans
  Pizza
Book
  SQL
  Oracle
Movie
  Avatar

Could you suggest a hierarchical query. I tried few, but could not get the desired result.

UPDATE realspirituals

Setup a fiddle here, if you want to try

Can you setup a fiddle and post what you tried? – Srini V May 09 '14 at 10:23 — Srini V, May 09 '14 at 10:23

score 3 · Accepted Answer · answered May 09 '14 at 10:42

This could be one solution

with tab as (select distinct category title, null parent from test
          union all 
         select title, category from test)
select lpad(' ', (level * 2 - 1), '-' ) ||  title as title
  from tab
  start with parent is null
connect by prior title = parent;

SqlFiddle

score 2 · Answer 2 · answered May 09 '14 at 10:45

Try this query:

SELECT CASE
  WHEN title IS NULL THEN category
  ELSE '- ' || title
END category_title
FROM (
  SELECT title, category FROM table
  union all
  SELECT DISTINCT NULL title, category from table
) t
ORDER BY category, title NULLS FIRST

Oracle query to fetch data in the form of a Tree

2 Answers2