Tyler Muth’s Blog

Technology with a focus on Oracle, Application Express and Linux

Hierarchical Query to Unordered List

Posted by Tyler Muth on February 26, 2009

I’m working on a project which involves hierarchical data that I want to output as an unordered list in HTML. I want to do this in a single SQL statement without resorting to PL/SQL loops. This is partially to post my solution, as well as to solicit ideas from anyone else who has an easier solution.

        lpad(' ',power(lvl,2))||
        -- open UL or just LI
        case when lvl !=  lag_lvl then '<ul><li>' else '    <li>' end ||
        ename ||
        -- close LI
        case when lvl = lead_lvl then '</li>'  end ||
        -- close ULs for all cases except last row
        case when lvl > lead_lvl then close_path ||'</li></ul>'  end ||        
        -- close ULs for last row
        case when lead_lvl is null then close_path end unordered_list               
        -- this query computes our lag / lead values and explodes the close_path
        select a.ename,a.lvl,nvl(lag(lvl) over (order by rnum),0) lag_lvl,
                lead(lvl) over (order by rnum) lead_lvl,
                    replace(the_path,lead(the_path) over (order by rnum)),
                    '~[[:digit:]]{1,2}','</li></ul>')  close_path
          from (
                -- this our hierarchical query.  we need rownum one level up for the order
                select ename,level lvl, rownum rnum,sys_connect_by_path(level,'~') the_path
                  from scott.emp
                  connect by prior empno = mgr
                  start with mgr is null) a
        ) b





8 Responses to “Hierarchical Query to Unordered List”

  1. Slightly easier:

    select lpad(' ',power(level,2)) ||
                case lag(level,1,0) over (order by rownum)
                  when level then '<li>'
                  else '<ul><li>'
                end ||
                ename ||
                case level - lead(level,1,1) over (order by rownum)
                  when -1 then null
                  when 0 then '</li>'
                else replace(lpad('*',level + 1 - lead(level,1,1) over (order by rownum),'*'),'*','</li></ul>')
              end unordered_list
         from scott.emp
      connect by prior empno = mgr
        start with mgr is null


  2. Tyler Muth said


    Outstanding! This is why I posted it here. I like your use of LPAD and replace vs my more complicated sys_connect_by_path and regexp_replace. I still might divide it into a 2 pass query to make it easier to understand. Pass 1 to for LAG, LEAD, and LPAD. Pass 2 for all of the case statements. It’s not that I feel it’s a better approach than yours, I personally think it would be a bit more readable. Additionally, someone trying to understand it can run the inner query first to see the result-set the case statements will operate on. 6 of one…

    I reformatted your comment with the sourcecode tags and deleted the other two per your request. Nice Work.


  3. Craig Martin said

    Pretty much the same thing, but getting rid of the replace, and puts back the 2 passes so you can see the original hierarchy query:

    	lpad(' ',power(lvl,2)) || 
    		when lvl  prv
    		then level_start
    		else item_start
    	end ||
    	ename ||
    		when lvl &gt; nxt
    		then rpad(level_end, (lvl - nxt + 1) * length(level_end), level_end) 
    		when lvl = nxt then item_end
    		ename,level lvl,
    		lead(level, 1, 1) over (order by rownum) nxt,
    		lag(level, 1, 0) over (order by rownum) prv,
    		'<ul>' level_start,
    		'</ul>' level_end,
    		'<li>' item_start,
    		'</li>' item_end
    	connect by
    		prior empno = mgr  
    	start with
    		mgr is null
  4. Craig Martin said

    Well clearly my code tags didn’t work very well. Let me know if you want me to try posting the formatted version again..

  5. […] MySQL, it’s about what Tyler Muth can do without resorting to PL/SQL loops, in this post: Hierarchical Query to Unordered List. Now, Pete Finnigan talks about CPUs and security in his blog, IOUG Critical Patch Update Survey […]

  6. […] Link to the original site Tags: Hierarchical Data, ORACLE BLOGS, Pl Sql, Sql Statement […]

  7. EWRichard said

    Amazing website!

  8. Peter said

    What if you wanted the output of a hierarchical query to be sorted in its entirety by name in alphabetical order. I need to create a LOV based on the results of a hierarchical query and I would prefer if the LOV was sorted alphabetically.

    Let me know what you think.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: