Hierarchical Query to Unordered List

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