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?
Rob van Wijk said
Slightly easier:
Regards,
Rob.
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
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:
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..
Log Buffer #138: A Carnival of the Vanities for DBAs said
[…] 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 […]
Hierarchical Query to Unordered List | Oracle said
[…] Link to the original site Tags: Hierarchical Data, ORACLE BLOGS, Pl Sql, Sql Statement […]
EWRichard said
Amazing website!
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!