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.

select 
        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               
 from(
        -- 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,
                regexp_replace(
                    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

Output

 <ul><li>KING                                                  
    <ul><li>JONES                                              
         <ul><li>SCOTT                                         
                <ul><li>ADAMS</li></ul></li></ul>              
         <ul><li>FORD                                          
                <ul><li>SMITH</li></ul></li></ul></li></ul>    
    <ul><li>BLAKE                                              
         <ul><li>ALLEN</li>                                    
             <li>WARD</li>                                     
             <li>MARTIN</li>                                   
             <li>TURNER</li>                                   
             <li>JAMES</li></ul></li></ul>                     
    <ul><li>CLARK                                              
         <ul><li>MILLER</li></ul></li></ul></li></ul>

Thoughts?

About these ads

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
    
    <ul><li>KING
        <ul><li>JONES
             <ul><li>SCOTT
                    <ul><li>ADAMS</li></ul></li></ul>
             <ul><li>FORD
                    <ul><li>SMITH</li></ul></li></ul></li></ul>
        <ul><li>BLAKE
             <ul><li>ALLEN</li>
             <li>WARD</li>
             <li>MARTIN</li>
             <li>TURNER</li>
             <li>JAMES</li></ul></li></ul>
        <ul><li>CLARK
             <ul><li>MILLER</li></ul></li></ul></li></ul>
    

    Regards,
    Rob.

  2. Tyler Muth said

    Rob,

    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.

    Tyler

  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:

    select
    	lpad(' ',power(lvl,2)) || 
    	case
    		when lvl  prv
    		then level_start
    		else item_start
    	end ||
    	ename ||
    	case
    		when lvl &gt; nxt
    		then rpad(level_end, (lvl - nxt + 1) * length(level_end), level_end) 
    		when lvl = nxt then item_end
    	end
    from
    (
    	select
    		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
    	from
    		emp  
    	connect by
    		prior empno = mgr  
    	start with
    		mgr is null
    )
    <code>
    
    Output:
    <code>
     KING
        JONES
             SCOTT
                    ADAMS
             FORD
                    SMITH
        BLAKE
             ALLEN
             WARD
             MARTIN
             TURNER
             JAMES
        CLARK
             MILLER
  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.

    Thanks!

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 93 other followers

%d bloggers like this: